When to use a foreign key in MySQL
- by Mel
Is there official guidance or a threshold to indicate when it is best practice to use a foreign key in a MySQL database?
Suppose you created a table for movies. One way to do it is to integrate the producer and director data into the same table. (movieID, movieName, directorName, producerName).
However, suppose most directors and producers have worked on many movies. Would it be best to create two other tables for producers and directors, and use a foreign key in the movie table?
When does it become best practice to do this? When many of the directors and producers are appearing several times in the column? Or is it best practice to employ a foreign key approach at the start? While it seems more efficient to use a foreign key, it also raises the complexity of the database.
So when does the trade off between complexity and normalization become worth it? I'm not sure if there is a threshold or a certain number of cell repetitions that makes it more sensible to use a foreign key.
I'm thinking about a database that will be used by hundreds of users, many concurrently.
Many thanks!
