If I have a relationship between two tables (both tables have their own primary keys) what should guide my decision as to which table should store the foreign key? I understand that the nature of the relationship probably matters (one-to-one, one-to-many, many-to-many, uni-directional, bi-directional), and probably access patterns matter too. What is a systematic way of making that decision though?
+5
A:
Which table is the child in the relationship?
Answer that, and you know which table needs the foreign key column, referencing the parent's [typically] primary key. That's for a one-to-many relationship...
A many-to-many would require you to add a third table, using the keys from both of the two tables as it's primary key.
OMG Ponies
2010-07-20 19:52:16
... and if there isn't a child / parent relationship. it's probably many-to-many in a join-table.
Wrikken
2010-07-20 19:53:28
Or it's a one-to-one relationship and OP should be asking himself why it's split into two tables in the first place.
Allan
2010-07-20 19:58:32
@Wrikken, @Allan: Excellent points, both of you.
OMG Ponies
2010-07-20 20:03:12
Wrikken
2010-07-20 20:22:37
A:
A foreign key is simply a field in one table that refers to a key field of another table. It's not absolutely critical to identify the foreign key field as such. That is, you don't need to explicitly add the FOREIGN KEY ... REFERENCES constraint to the table for it to be a foreign key. When you join the two tables together, the primary key of the parent table will be set equal to the foreign key of the child table. Whichever one is not the primary key is the foreign key.
In one-to-many relationships, the FK goes on the "many" side. It can't go on the "one" side because that's where the PK goes and the definition of a primary key includes disallowing duplicates.
If you have a many-to-many relationship, you'll need to re-work the tables so you end up with two one-to-many relationships and an intermediate resolution table.
Barry Brown
2010-07-20 19:56:47
+1
A:
"What is a systematic way of making that decision though?"
There appear to be two choices: The "One" side as FK's to the "Many side", or the "Many" Side has FK's to the "One" side.
Let's actually look a the choices.
All the rows of the "Many" side can easily reference one row on the "One" side.
The one row on the "One" side cannot ever reference ALL of the rows on the "Many" side.
Only one technique works: "Many" side has FK to "One" side.
There is only one actual implementation choice. There's no "decision".
S.Lott
2010-07-20 20:38:42
In short: it's a matter of cardinality. If you have a many-to-one relationship, the key should reside on the 'many' side. If it is one-to-perhaps-one, on the 'one' side. If it is many-to-many, you need an intermediate table. If it's one-to-one, you're free to choose.
reinierpost
2010-07-20 22:26:09