Where should I store a foreign key?

mysql database database-design data-modeling
vicatcu picture vicatcu · Jul 20, 2010 · Viewed 16.7k times · Source

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

OMG Ponies picture OMG Ponies · Jul 20, 2010

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.

Related questions

single fixed table with multiple columns vs flexible abstract tables

I was wondering if you have a website with a dozen different types of listings (Shops, Restaurants, Clubs, Hotels, Events) that require different fields, is there a benefit of creating a table with columns defined like so Example Shop: shop_…

Read More
Auto Generate Database Diagram MySQL

I'm tired of opening Dia and creating a database diagram at the beginning of every project. Is there a tool out there that will let me select specific tables and then create a database diagram for me based on a …

Read More
What does character set and collation mean exactly?

I can read the MySQL documentation and it's pretty clear. But, how does one decide which character set to use? On what data does collation have an effect? I'm asking for an explanation of the two and how to choose …

Read More