如何在mysql规范化数据库中使用主键

The general form of a many-to-many relationship is to create three two-column tables (e.g. to relate favorite movies of members). Two tables are list of movies and members. In these two tables, we assign an auto_increment ID (which is primary key).Then storing relationship of these two IDs in the third table.

多对多关系的一般形式是创建三个两列表(例如，关联成员最喜欢的电影)。两个表格是电影和成员的列表。在这两个表中，我们分配了一个auto_increment ID(它是主键)。然后在第三个表中存储这两个id的关系。

I wonder why we use ID for these two columns? We can have two one-column tables as lists of movies and persons; and then create the relationship table relating movies to persons as:

我想知道为什么我们对这两列使用ID ?我们可以有两个一栏的表格作为电影和人物的列表;然后创建关系表，将电影与人联系在一起:

Fred   Gladiator
Brian  Godfather
Fred   Godfather

The only disadvantage of this method is that searching integer-only table is easier to find relationships. Instead we have reduced two indexed columns.

这种方法的唯一缺点是搜索纯整数表更容易找到关系。相反，我们减少了两个索引列。

Any idea?

任何想法?

3 个解决方案

#1

I wonder why we use ID for these two columns?

我想知道为什么我们对这两列使用ID ?

I think it's usually cargo cult programming.

我认为这通常是货物崇拜的节目。

If you can't distinguish one Fred from another without an arbitrary, meaningless id number, then you can't distinguish one Fred from another with an arbitrary, meaningless id number.
如果没有任意的、无意义的id号就不能区分一个Fred和另一个Fred，那么就不能用任意的、无意义的id号区分一个Fred和另一个Fred。
If you anticipate changes to either of the names, then you should cascade updates. (Many Oracle developers insist that keys must be immutable. That's not because mutable, natural keys are "bad"; it's because Oracle doesn't support ON UPDATE CASCADE.)
如果您预期任何一个名称的更改，那么您应该对其进行级联更新。(许多Oracle开发人员坚持密钥必须是不可变的。这并不是因为可变的，自然键是“坏的”;这是因为Oracle不支持更新级联。
There's a widespread--but incorrect--belief that id numbers are simply "faster", possibly because dbms engines are "optimized for joins on id numbers". Whether id numbers are faster in joins depends on the width of your tables, the content of the natural key, the number of rows, the number of joins required, page size, and the nature of your queries. What's often overlooked is that using natural keys usually reduces the number of joins. Sometimes, natural keys eliminate all the joins, even in tables that are in 5NF.
有一种普遍但不正确的看法，认为id号只是“更快”，这可能是因为dbms引擎“针对id号的连接进行了优化”。id号在连接中是否更快取决于表的宽度、自然键的内容、行数、所需的连接数、页面大小和查询的性质。通常被忽视的是，使用自然键通常会减少连接的数量。有时，自然键会消除所有连接，甚至在5NF中的表中也是如此。

See this recent SO answer for some measurements.

请参阅最近的SO答案以获得一些度量。

Don't guess. Measure.

不猜。衡量。

#2

In any real world scenario the two tables (movies and persons) will have a lot more columns (like movies.year, persons.lastname, persons.firstname etc). In this case it makes sense to have a single primary key column in each of the tables (which could as well be a non auto-increment column e.g. movies.name) for the many to many relation.

在任何真实场景中，这两个表(电影和人物)将有更多的列(如电影)。年人。姓,人。firstname等等)。在这种情况下，对于许多到许多关系来说，在每个表中都有一个主键列(也可以是一个非自动递增的列，例如movies.name)是有意义的。

But if the situation is as trivial as you suggest then your solution should be good enough. I don't think the disadvantage you mention will really be a concern.

但如果情况像你建议的那样微不足道，那么你的解决方案应该足够好。我不认为你提到的缺点会真正引起关注。

Aditya

#3

I see a number of potential issues:

我看到了一些潜在的问题:

How do you distinguish between multiple Freds?
你如何区分多重朋友?
What happens if a movie or person changes name (pretty common when a movie is in development)? Now you have to go and make sure every table that references it is updated, whereas an ID would remain constant.
如果一个电影或一个人改变了名字(在电影开发中很常见)会发生什么?现在，您必须确保每一个引用它的表都已更新，而ID将保持不变。

#1