I have to model a situation where I have a table Chequing_Account (which contains budget, iban number and other details of the account) which has to be related to two different tables Person and Corporation which both can have 0, 1 or many chequing accounts.
In other words I have two 1-to-many relationships with the same table Chequing account
I would like to hear solutions for this problem which respect the normalization requirements.
Most solutions I have heard around are:
1) find a common entity of which both Person and Corporation belong and create a link table between this and the Chequing_Account table, this is not possible in my case and even if it were I want to solve the general problem and not this specific instance.
2) Create two link tables PersonToChequingAccount and CorporationToChequingAccount which relate the two entities with the Chequing Accounts. However I don't want two Persons to have the same chequing account, and I don't want to have a natural person and a Corporation to share a chequing account! see this image
3) Create two foreign keys in Chequing Account which point to Corporation and Natural Person, however I would thus enforce that a Person and a Company can have many chequing accounts however I would have to manually ensure that for each ChequingAccount row not both relations point to Corporation and Natural person because a checquing account is either of a corporation or of a Natural Person. see this image
Is there any other cleaner solution to this problem?
Best Answer
Relational databases are not built to handle this situation perfectly. You have to decide what is most important to you and then make your trade-offs. You have several goals:
The problem is that some of these goals compete with one another.
Sub-Typing Solution
You could choose a sub-typing solution where you create a super-type that incorporates both corporations and persons. This super-type would probably have a compound key of the natural key of the sub-type plus a partitioning attribute (e.g.
customer_type). This is fine as far as normalization goes and it allows you to enforce referential integrity as well as the constraint that corporations and persons are mutually exclusive. The problem is that this makes data retrieval more difficult, because you always have to branch based oncustomer_typewhen you join account to the account holder. This probably means usingUNIONand having a lot of repetitive SQL in your query.Two Foreign Keys Solution
You could choose a solution where you keep two foreign keys in your account table, one to corporation and one to person. This solution also allows you to maintain referential integrity, normalization and mutual exclusivity. It also has the same data retrieval drawback as the sub-typing solution. In fact, this solution is just like the sub-typing solution except that you get to the problem of branching your joining logic "sooner".
Nevertheless, a lot of data modellers would consider this solution inferior to the sub-typing solution because of the way that the mutual exclusivity constraint is enforced. In the sub-typing solution you use keys to enforce the mutual exclusivity. In the two foreign key solution you use a
CHECKconstraint. I know some people who have an unjustified bias against check constraints. These people would prefer the solution that keeps the constraints in the keys."Denormalized" Partitioning Attribute Solution
There is another option where you keep a single foreign key column on the chequing account table and use another column to tell you how to interpret the foreign key column (RoKa's
OwnerTypeIDcolumn). This essentially eliminates the super-type table in the sub-typing solution by denormalizing the partitioning attribute to the child table. (Note that this is not strictly "denormalization" according to the formal definition, because the partitioning attribute is part of a primary key.) This solution seems quite simple since it avoids having an extra table to do more or less the same thing and it cuts the number of foreign key columns down to one. The problem with this solution is that it doesn't avoid the branching of retrieval logic and what's more, it doesn't allow you to maintain declarative referential integrity. SQL databases don't have the ability to manage a single foreign key column being for one of multiple parent tables.Shared Primary Key Domain Solution
One way that people sometimes deal with this issue is to use a single pool of IDs so that there is no confusion for any given ID whether it belongs to one sub-type or another. This would probably work pretty naturally in a banking scenario, since you aren't going to issue the same bank account number to both a corporation and a natural person. This has the advantage of avoiding the need for a partitioning attribute. You could do this with or without a super-type table. Using a super-type table allows you to use declarative constraints to enforce uniqueness. Otherwise this would have to be enforced procedurally. This solution is normalized but it won't allow you to maintain declarative referential integrity unless you keep the super-type table. It still does nothing to avoid complex retrieval logic.
You can see therefore that it isn't really possible to have a clean design that follows all of the rules, while at the same time keeping your data retrieval simple. You have to decide where your trade-offs are going to be.