I searched this forum for ideas but could not find an answer. Here is the problem i am dealing with.
I have three tables: case,address and name. case and address have a many to many relationship and so does name and address. I created two intersection tables: case_name with composite: caseid,nameid and address_name with composite nameid,addressid.
What i am wondering now is, what about instead of creating two intersection tables, i just create one intersection table with three composite keys: a case_name_address table with a caseid, a nameid and an addressid as a composite. Are there any advantages or disadvantages to using a three key composite primary key?
Are there any advantages or disadvantages to using a three key composite primary key?
the advantages are, if you only have one relationship, in which all three ids have to be present, then this 3-key design nicely accommodates that
the disadvantages are, if you can have a relationship between case and address, without regard to the name involved, or a relationship between name and address, without a case being involved, then this 3-key design is rubbish for supporting those