Where should I store a foreign key?

Question

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?

Answer 1

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.

Answer 2

"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".