Dave Tolls wrote:I'm a bit confused here...probably a Monday thing.
But could you show an example of what the two versions look like?
When I think of a Many-to-Many relationship I think:
TABLE_1 with an ID column plus others.
TABLE_2 with an ID column plus others.
MAP_TABLE_1_2 with FK on TABLE_1.ID and FK on TABLE_2.ID, and a PK on both those columns.
Or is there something else you are actually asking?
Assume you have table 1 called "Table1" and another table called "Table2", with PKs TAB1_ID and TAB2_ID.
Now, in the M:M table, you could either have:
(1) a (single) MAP_TABLE_TABFK with another field (a discriminator to say which table the FK is for) or
(2) two separate FK fields in the MAP table, one for each FK.
I chose (1) so there wouldn't always be a blank field (one FK or the other) in each record and it's cleaner.
So you might have data, assuming (1) like this in the MAP table:
MAP_ID MAP_TAB_FK MAP_DISC
1 100 T1
2 100 T2
3 222 T1
-----------------
Instead of (2):
-----------------
MAP_ID MAP_TAB1_FK MAP_TAB2_FK
1 100
2 100
3 222
----
Does that clarify what I was asking?
-- mike
---