数据库设计：将主键作为单独的字段存储在同一个表中
我有一个表必须引用同一个表的另一条记录。这是一个例子：
Customer
********
ID
ManagerID (the ID of another customer)
...
我对此有一种不好的预感。我的另一个想法是只拥有一个单独的表来存储关系。
CustomerRelationship
***************
ID
CustomerID
ManagerID
我觉得我可能把这样一件微不足道的事情复杂化了，但是，我想了解一下针对这种特定情况的最佳方法？
谢谢。
I have a table that must reference another record, but of the same table. Here's an example:
Customer
********
ID
ManagerID (the ID of another customer)
...
I have a bad feeling about doing this. My other idea was to just have a separate table that just stored the relationship.
CustomerRelationship
***************
ID
CustomerID
ManagerID
I feel I may be over complicating such a trivial thing however, I would like to get some idea's on the best approach for this particular scenario?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
第一个设计没有什么问题。第二个，你有一个“中间”表，用于多对多关系，我认为这不是你的。
顺便说一句，该中间表没有自己的 ID。
There's nothing wrong about the first design. The second one, where you have an 'intermediate' table, is used for many-to-many relationships, which i don't think is yours.
BTW, that intermediate table wouldn't have and ID of its own.
为什么你对此有一种“不好的预感”？表引用自己的主键是完全可以接受的。引入辅助表只会增加查询的复杂性并对性能产生负面影响。
Why do you have a "bad feeling" about this? It's perfectly acceptable for a table to reference its own primary key. Introducing a secondary table only increases the complexity of your queries and negatively impacts performance.
一个客户可以有多个经理吗？如果是这样，那么您需要一个单独的表。
否则，单表就可以了。
Can a Customer have multiple managers? If so, then you need a separate table.
Otherwise, a single table is fine.
您可以使用第一种方法。另请参阅使用自联接
You can use the first approach. See also Using Self-Joins
第一种方法绝对没有问题，事实上，Oracle 至少从版本 6 开始就包含了 SQL 的“CONNECT BY”扩展，旨在直接支持这种类型的层次结构（并且可能使 Oracle 值得考虑作为您的数据库，如果您我们将会做很多这样的事情）。
您需要在没有类似功能的数据库中进行自连接，但这也是一个完美的标准解决方案。
There's absolutely nothing wrong with the first approach, in fact Oracle has included the 'CONNECT BY' extension to SQL since at least version 6 which is intended to directly support this type of hierarchical structure (and possibly makes Oracle worth considering as your database if you are going to be doing a lot of this).
You'll need self-joins in databases which don't have something analogous, but that's also a perfectly fine and standard solution.
作为一名程序员，我喜欢第一种方法。我喜欢少放几张桌子。这里我们甚至没有讨论标准化，为什么我们需要更多的表？那只是我。
As a programmer I like the first approach. I like to have less number of tables. Here we are not even talking of normalization and why do we need more tables? That is just me.
请遵循此处的 KISS 原则：保持简单（愚蠢 | 愚蠢 | 螺柱 | [无论您喜欢以 S 开头的任何绰号]）。除非您有理由需要更多，否则请使用一张桌子。
请注意，如果最终是一对多/多对多关系，您可以将现有列提取到自己的表中，然后填写新条目。
Follow the KISS principle here: Keep it simple, (silly | stupid | stud | [whatever epithet starting with S you prefer]). Go with one table, unless you have a reason to need more.
Note that if the one-to-many/many-to-many relationship ends up being the case, you can extract the existing column into a table of its own, and fill in the new entries at that time.
我建议避免此类自引用表的唯一原因是 SQL Server 确实有一些地方存在自引用表的限制。
其一，如果您碰巧需要索引视图，然后您会发现，如果视图定义中使用的表之一确实是自引用的，您将无法在视图上创建聚集索引:-(
但除此之外 - 设计本身是合理的并且绝对有效 - 去做吧！我总是喜欢让事情尽可能简单（但没有比这更简单的了）
。
The only reason I would ever recommend avoiding such self-referecing tables is that SQL Server does have a few spots where there are limitations with self-referencing tables.
For one, if you ever happen to come across the need for an indexed view, then you'd find out that if one of the tables used in a view definition is indeed self-referencing, you won't be able to create a clustered index on your view :-(
But apart from that - the design per se is sound and absolutely valid - go for it! I always like to keep things as simple as possible (but no simpler than that).
Marc