我需要为我的关系表创建代理键吗？
我有一个多对多实体的关系表。
作者 ID | book_id
我需要添加relation_id吗？我应该能够使用两个 id 进行识别。
谢谢
I've a relationship table for my many-to-many entities.
author_id | book_id
Do I need to add relation_id? I should be able to identify using both id.
Thank you
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
大多数时候，在像这样的连接表中，只需将两者设置为组合 PK 就可以了。
Most of the time in a joinn table like this just setting both as a combined PK is fine.
您永远不需要代理键，因为原则上您始终可以使用其他键来代替。
在你的问题中，我认为你的意思不是“关系表”，更不是“关系”。我认为你的意思是“一张有多个外键的表”。具有多个外键的表与其他表没有什么不同，它们的设计原则也不需要不同。选择键的基础与选择任何其他表的键相同。
You never need a surrogate key because you can always in principle use some other key instead.
In your question I don't think you mean "relational table", much less "relation". What I think you mean is "a table with more than one foreign key". Tables with more than one foreign key are not different to other tables and the design principles for them don't need to be different either. Choose the keys on the same basis you would for any other tables.
我更喜欢在每个表上都有一个单列自动增量键。它使删除和更新变得更加简单。
I prefer having a single column auto-inc key on every table. It makes deleting and updating much simpler.
您不需要添加代理键。我不会。
You don't need to add a surrogate key. I would not.
由于您使用的是 Doctrine，请尽量不要在 RDBMS 级别上考虑太多（至少，大多数时候不是）。
如果您有两个具有多对多关系的实体，则应该忘记代理键。事实上，您几乎应该忽略关系表存在的事实。您只有两个相关的实体类型。
现在，如果您需要存储有关关系本身的元数据（例如，向用户授予徽章的日期），那么您将超越简单的多对多，并且您需要自己对该关系进行建模 - 通过创建一种新的关系实体（例如 UserBadge）。当然，该实体会有一个 ID。
您正在使用 ORM，考虑实体，而不是表（大多数时候）！
Since you're using Doctrine, try not to think too much on the RDBMS level (at least, not most of the time).
If you have two Entities with a ManyToMany relationship, you should forget about the surrogate key. In fact, you should pretty much ignore the fact that the relationship table exists. You simply have two related entity types.
Now, if you need to store metadata about the relationship itself (for instance the date a badge was awarded to a user), you're going beyond a simple ManyToMany, and you need to model that relationship yourself -- by creating a new kind of entity (a UserBadge, for instance). That entity, of course, would have an id.
You're using an ORM, think about entities, not about tables (most of the time)!
这里有两个问题。
您确实需要对书/列组合进行唯一约束以防止重复。即使您使用了 auto-inc 代理键，您也必须执行此操作。
其次，现在许多框架不知道如何处理除整数键之外的任何内容，因此您可能还想添加整数列。但在这种情况下，您将需要两者。
There are two issues here.
You DO need a unique constraint on the book/column combination to prevent duplicates. You have to do this even if you put on an auto-inc surrogate key.
Second, many frameworks these days don't know how to deal with anything except an integer key, so you may want to ALSO put on the integer column. But in this case you would need both.
您绝对不需要它，但在 SQL 控制台上玩时它可能会很方便，特别是当该对的两个标识符都很长并且输入起来很烦人时。
另一方面，它打破了3NF。如果您不小心，可能会得到两条具有不同代理键和同一对
(book_id,author_id)值的记录。您将有两个索引来确保两个唯一性约束；这会减慢插入和更新速度。此外，为了提高效率，您可能希望避免额外的列。如果您的表有大量记录，则拥有额外的列会降低将其缓存在内存中的效率，并且使用它的联接会因磁盘 I/O 更频繁而减慢。
You strictly don't need it, but it might be handy while playing on SQL console, especially if both identifiers of the pair are long and tiresome to type.
On the other hand, it breaks 3NF. If you're not careful you can end up with two records with different surrogate keys and same pair of
(book_id, author_id)values. You'll have two indices to ensure two uniqueness constraints; this will slow down inserts and updates.Also you might want to avoid an extra column for efficiency. If your table has great many records, having an extra column will make caching it in memory less efficient, and joins using it will be slowed by disk I/O more often.