Cyclic reference in a database table

Question

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?

Answer 1

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.

• See this article on performing hierarchical queries using Common Table Expressions.
• See this MySQL article detailing some of the queries used in self referential relationships.

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.