Is there official guidance or a threshold to indicate when it is best practice to use a foreign key in a MySQL database?
是否有正式指导或阈值来指示何时在MySQL数据库中使用外键是最佳做法？
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).
假设您为电影创建了一个表。一种方法是将生产者和导演数据集成到同一个表中。 （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!
非常感谢！
2 个解决方案
#1
11
there are some official guidelines for this. they're called normal forms, and the practice of putting your database into them is called normalization: http://en.wikipedia.org/wiki/Database_normalization
这方面有一些官方指导方针。它们被称为普通形式，将数据库放入其中的做法称为规范化：http：//en.wikipedia.org/wiki/Database_normalization
if you take a db class in college, they'll probably teach you 3nf or bcnf. i've always found those approaches to be a bit heavy-handed, but i have enough experience in db design that i find these questions to be basically intuitive at this point...
如果你在大学读一个db课程，他们可能会教你3nf或bcnf。我总是发现这些方法有点笨拙，但我有足够的数据库设计经验，我发现这些问题基本上是直观的...
in your example, you definitely want to use foreign key constraints. a many-to-one relationship is best expressed that way. it will make selecting movies a bit slower, because you'll have to do a join on the 'people' table and the 'movies' table - possibly many joins depending on how many 'people' fields the movies table has.
在您的示例中，您肯定希望使用外键约束。这种多对一的关系最好用这种方式表达出来。它会使选择电影的速度变慢，因为你必须在'人物'表和'电影'表上进行连接 - 可能有很多连接，具体取决于电影表有多少'人'字段。
but the advantage is that you can easily manage the people themselves. if you want to change the spelling of a person name, you don't have to scan the whole table looking for that person in each field. you can avoid having the same person in the db several times with slight differences in spelling. you can set actions to take if a person is deleted. you can easily count how many different roles a person has had.
但优点是你可以轻松地管理人们自己。如果要更改人名的拼写，则不必扫描整个表，在每个字段中查找该人。您可以避免在数据库中多次使用同一个人，但拼写略有不同。您可以设置删除某人时要采取的操作。您可以轻松计算一个人有多少不同的角色。
don't forget, if you want to use foreign keys, you must make your tables innodb in mysql.
不要忘记，如果你想使用外键，你必须在mysql中创建你的表innodb。
#2
4
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).
假设您为电影创建了一个表。一种方法是将生产者和导演数据集成到同一个表中。 （movieID，movieName，directorName，producerName）。
That's far too unnormalized. You're repeating data.
这太不正常了。你在重复数据。
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?
但是，假设大多数导演和制片人都在制作过许多电影。是否最好为生产者和导演创建另外两个表，并在电影表中使用外键？
Also suppose that a person may work on one movie as a producer and the next as a director. A single person could also be credited as director, producer, writer and actor on a single movie!
还假设一个人可以作为制片人在一部电影上工作，而在下一部作为导演。一个人也可以在一部电影中被认为是导演，制片人，作家和演员！
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.
什么时候成为最佳做法？许多董事和制片人多次出现在专栏中？或者最好在一开始就采用外键方法？虽然使用外键似乎更有效，但它也增加了数据库的复杂性。
You will want to master foreign keys, relationships (especially 1-to-many and many-to-many), and normal forms right from the start. They will become second nature in no time.
您将需要从一开始就掌握外键，关系（特别是1对多和多对多）以及普通表单。他们很快就会成为第二天性。
#1
11
there are some official guidelines for this. they're called normal forms, and the practice of putting your database into them is called normalization: http://en.wikipedia.org/wiki/Database_normalization
这方面有一些官方指导方针。它们被称为普通形式，将数据库放入其中的做法称为规范化：http：//en.wikipedia.org/wiki/Database_normalization
if you take a db class in college, they'll probably teach you 3nf or bcnf. i've always found those approaches to be a bit heavy-handed, but i have enough experience in db design that i find these questions to be basically intuitive at this point...
如果你在大学读一个db课程，他们可能会教你3nf或bcnf。我总是发现这些方法有点笨拙，但我有足够的数据库设计经验，我发现这些问题基本上是直观的...
in your example, you definitely want to use foreign key constraints. a many-to-one relationship is best expressed that way. it will make selecting movies a bit slower, because you'll have to do a join on the 'people' table and the 'movies' table - possibly many joins depending on how many 'people' fields the movies table has.
在您的示例中，您肯定希望使用外键约束。这种多对一的关系最好用这种方式表达出来。它会使选择电影的速度变慢，因为你必须在'人物'表和'电影'表上进行连接 - 可能有很多连接，具体取决于电影表有多少'人'字段。
but the advantage is that you can easily manage the people themselves. if you want to change the spelling of a person name, you don't have to scan the whole table looking for that person in each field. you can avoid having the same person in the db several times with slight differences in spelling. you can set actions to take if a person is deleted. you can easily count how many different roles a person has had.
但优点是你可以轻松地管理人们自己。如果要更改人名的拼写，则不必扫描整个表，在每个字段中查找该人。您可以避免在数据库中多次使用同一个人，但拼写略有不同。您可以设置删除某人时要采取的操作。您可以轻松计算一个人有多少不同的角色。
don't forget, if you want to use foreign keys, you must make your tables innodb in mysql.
不要忘记，如果你想使用外键，你必须在mysql中创建你的表innodb。
#2
4
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).
假设您为电影创建了一个表。一种方法是将生产者和导演数据集成到同一个表中。 （movieID，movieName，directorName，producerName）。
That's far too unnormalized. You're repeating data.
这太不正常了。你在重复数据。
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?
但是，假设大多数导演和制片人都在制作过许多电影。是否最好为生产者和导演创建另外两个表，并在电影表中使用外键？
Also suppose that a person may work on one movie as a producer and the next as a director. A single person could also be credited as director, producer, writer and actor on a single movie!
还假设一个人可以作为制片人在一部电影上工作，而在下一部作为导演。一个人也可以在一部电影中被认为是导演，制片人，作家和演员！
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.
什么时候成为最佳做法？许多董事和制片人多次出现在专栏中？或者最好在一开始就采用外键方法？虽然使用外键似乎更有效，但它也增加了数据库的复杂性。
You will want to master foreign keys, relationships (especially 1-to-many and many-to-many), and normal forms right from the start. They will become second nature in no time.
您将需要从一开始就掌握外键，关系（特别是1对多和多对多）以及普通表单。他们很快就会成为第二天性。