Composite Key Theory

[protocode]

I have two tables - A & B

A holds names & addresses
B holds the relationships between the primary keys in table A as a composite key

Example:

Table B Fields:
date, PriKy1,PriKy2 -> 2010-06-14, AA,BB

The example states that primary key AA from Table A and BB from Table A are "together". This row should only happen once.

But if the composite keys are reversed then a new row is generated. AA, BB should equal BB,AA

Also, AA can have more than one other primary key. So AA, CC ; AA, DD

What other kind of table set up should I consider?

[tesuji]

Hi
seems to be a nice theory where you want to define a recursive relationship which is able to store trees and hierarchies. You should get acquaint with famous Joe Celko, the guy how wrote some important books only about this theory ---> google for Joe Celko's trees and hierarchies, and you probably get beatific then.

Also consider that Joe Celko ist the guy who stated the almost most important rule for designing relational databases:

"I call people who put a magical, universal ID-column with an auto-increment on all their tables ID-iots (by Joe Celko).

As for your example:

First create Table A:
create table a (ak int not null, x char, primary key (ak));

then table b which references itself recursively:
create table b (bk int not null, y char, af int not null, 
  primary key (bk, af), foreign key (af) references a on delete cascade);

-- tesu