数据库表中的循环引用
我很羞于问这个问题，但最近出现了一种情况，我需要为彼此相关的三种不同类型的银行实体创建一个表。让我解释一下。
想象一个 BANK 表，其中包含管理银行、运营农村分行的常规银行、或该银行下运营的农村分行或不属于此层次结构但仅与农村分行进行交易的零售银行分行的详细信息。
之前，我决定为这些数据建立 4 个不同的表，并带有 FK 约束（即，管理银行、经营农村分行的银行、农村分行和零售银行分行各一个）。但是，当我继续创建 TRANSACTION 表时，我感到很困惑，因为交易可能发生在任何这些实体之间（例如：农村分支机构与零售分支机构之间、农村分支机构本身之间等）。这意味着我不仅需要保留“来源”和“来源”的记录。银行实体的“目标”ID，但也保留一些数据来帮助应用程序逻辑确定要加入哪个表进行查询。我觉得这很糟糕。
此外，还有一个 USER 表，用户可以属于其中任何一个表实体，这里也有 4 个不同的银行实体表，我如何知道用户是否属于农村分行或零售分行或管理银行？
因此，我创建了一个银行表（本质上是因为它们是相似的实体，因为它们可以相互交易）。我在表中添加了一个 PARENT 列，该列将保存上级机构的 ID 值（我使用 FK 实现的关系）。 Retail 分支没有父级，因此该值是 NULL 等等，
我现在看到的问题是 BANK 表中存在 PK/FK 关系，这是一个循环引用
。还有什么出路呢？
I am quite ashamed to ask this, but recently there has been a situation where I need to create a single table for three different types of banking entities that are related to each other. Let me explain.
Imagine a BANK table that holds details of either a Governing Bank, or a regular Bank that operates rural branches, or the rural branches operating under this Bank or Retail Bank Branches that don't fall in this hierarchy but only transact with rural branch.
Previously, I decided on having 4 different tables for these, with FK constraints (i.e one each for Governing Bank, Bank that operates rural branches, rural branch and Retail Bank Branch). But when I moved on to create the TRANSACTION table I was perplexed since transactions could happen between any of these entities (example: between rural branch & Retail Branch, between rural branches themselves etc). This meant that I would not only have to keep a record of the "Source" & 'Destination" IDs of the Banking Entity, but also keep some data to help the application logic determine which TABLE to JOIN on for querying. I felt that was bad.
Moreover, there is a USER table and the user could belong to any of these entities, here too having 4 different tables of banking entities was problematic. How do I know if the User belongs to a rural branch or Retail Branch or the Governing bank?
Therefore, I created a single BANK table (essentially because they are similar entities, as they can transact with each other). I added a PARENT column in the table that would hold the value of ID of the parent institution (relationship I otherwise achieved using FKs). So a rural branch will have ID of Operating Bank in its parent column. Retail branches have no parents hence the value is NULL there and so on.
The problem I see now is that there is a PK/FK relationship in the BANK table, a cyclic reference.
My question is is: how bad is this? And what could be a way out?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
拥有自我参照关系并不罕见。一个缺点是许多 RDBMS 不允许您对自引用关系执行级联删除。除此之外，这种等级关系没有任何巨大的缺陷。许多数据库解决方案甚至支持扩展功能来促进这种类型的关系。
此外，我是否建议您拥有此银行表，但保留银行类型的辅助表，以便每个银行在银行表中都有一条记录，并且另外在保存银行类型特定的其他表之一中也有一条记录扩展属性。这样，关系仍然是集中的，用户仍然可以使用单个 FK 绑定到银行表，但您的银行表不会与所有不同银行类型的扩展属性混淆。
Having self referential relationships isn't uncommon. One downfall is that many RDBMS don't allow you to perform cascading deletes on self referential relationships. Other than that, this type of hierarchical relationship doesn't have any huge pitfalls. Many of the database solutions even support extended functionality to facilitate this type of relationship.
Additionally, might I recommend that you have this Bank table, but keep the secondary tables for the bank types such that each bank would have a record in the Bank table, and additionally would have a record in one of the other tables holding bank type specific extended properties. That way the relationships would still be centralized, the users could still be tied to the Bank table using a single FK, but your Bank table wouldn't be muddled with extended properties for all the different bank types.