什么时候在MySQL中使用外键 [英] When to use a foreign key in MySQL

问题描述

有没有官方指导或阈值来表明何时是在MySQL数据库中使用外键的最佳实践？

Is there official guidance or a threshold to indicate when it is best practice to use a foreign key in a MySQL database?

假设您为电影创建了一个表。一种方法是将生产者和导演数据集成到同一个表中。 （movieID，movieName，directorName，producerName）。

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.

谢谢！

推荐答案

有一些官方指南。它们称为正常表单，将数据库放入其中的做法称为规范化： http：// en .wikipedia.org / wiki / Database_normalization

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

如果你在大学学习一个db类，他们可能会教你3nf或bcnf。我总是发现这些方法有点重度，但我有足够的经验，在数据库设计，我发现这些问题基本上直观的在这一点...

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

在您的示例中，您肯定要使用外键约束。一个多对一的关系最好表达出来。它会使选择电影有点慢，因为你必须在人表和电影表上进行连接 - 可能有很多连接，取决于电影表有多少人字段。

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.

不要忘记，如果你想使用外键，你必须使你的表innodb在mysql。

don't forget, if you want to use foreign keys, you must make your tables innodb in mysql.

这篇关于什么时候在MySQL中使用外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！