如何设计对具有添加关系的单个数据库表的循环引用？
我不确定如何最好地表达这个问题，但本质上我有一个联系人表，而不是执行典型的操作 - 联系人引用包含配偶信息的表和包含孩子的表，我想要每个将这些人作为联系人，然后定义这些联系人之间的关系（兄弟、姐妹、孩子、配偶等）。因此，联系人将存在于单个表中，但我无法确定如何根据联系人 ID 和关系类型最好地定义关系。任何建议将不胜感激。
I'm not sure how best to phrase the question, but essentially I have a table of contacts, and instead of doing the typical -- a contact has a reference to a table with spouse information, and a table with children, I want each of those people to be a contact, but then define a relationship between those contacts (brother, sister, child, spouse, etc.). So the contacts would exist in a single table, but I'm having trouble determining how best to define the relationship based upon their contact id and the relationship type. Any advice would be appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
CONTACTS表contact_id，pkCONTACT_RELATIONSHIP_TYPE_CODE表contact_relationship_type_code，pkdescriptionCONTACTS_RELATIONS表parent_contact_id，pk，CONTACTS的外键 表child_contact_id，pk，CONTACTS的外键表contact_relationship_type_code，CONTACT_RELATIONSHIP_TYPE_CODE表的外键如果您认为需要支持一对人的多种关系类型，请添加
CONTACTS_RELATIONS.contact_relationship_type_code列到复合主键CONTACTStablecontact_id, pkCONTACT_RELATIONSHIP_TYPE_CODEtablecontact_relationship_type_code, pkdescriptionCONTACTS_RELATIONStableparent_contact_id, pk, foreign key toCONTACTStablechild_contact_id, pk, foreign key toCONTACTStablecontact_relationship_type_code, foreign key toCONTACT_RELATIONSHIP_TYPE_CODEtableIf you see the need to support multiple relationship types to a pair of people, add the
CONTACTS_RELATIONS.contact_relationship_type_codecolumn to the composite primary key这称为自连接，它非常常见并且相当容易提供您上面提到的功能。请查看这篇文章。
This is called a self join, it is pretty common and fairly easy to provide the functionallity you mention above. Take a look at this article.
只需实现一个包含四列的相交表 - key、contactid #1、contact id#2 和关系。
为什么要这样做呢？因为一个联系人可以有多个关系。
Just implement an intersect table with four columns - key, contactid #1, contact id#2, and relationship.
Why do it this way? Because a contact can have several relationships.