Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

http://i41.tinypic.com/35i6kbk.png

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

http://i40.tinypic.com/1rpv9z.png

Is there any other cleaner solution to this problem?

share|improve this question
    
Have you thought about having for e.g. a OwnerTypeID in the ChecquingAccount table, with 1=Corporation and 2=NaturalPerson? That way you only need one OwnerID in the ChecquingAccount table, which you can index along with the OwnerTypeID. –  RoKa May 17 '13 at 14:41
    
I need not only to know whether it is a corporation or natural person but also to know the respective id, so I need an id number and not only a value of 1 or 2! Solution 3 is what I found here I have two columns with ids to corporation or natural person –  dendini May 17 '13 at 14:44
2  
Yes, solution is a valid option. In most DBMS you can enforce that only one of the two FKs is "active" with a check constraint: CHECK (CorporationID IS NOT NULL AND NaturalPersonID IS NULL OR CorporationID IS NULL AND NaturalPersonID IS NOT NULL) I much prefer solution 1 though (but that's just me). It's much "cleaner". –  ypercube May 17 '13 at 14:47
    
Yes, I understand, but you could have in the ChecquingAccount table a record of OwnerTypeID=1 and OwnerID=123, indicating that it is a type Corporation, therefore ID 123 in the Corporation table. The OwnerTypeID tells you which table, and the OwnerID tells you the ID in that table. –  RoKa May 17 '13 at 14:50
1  
How is option #1 impossible? The word "Corporation" basically means "a business which is legally a person," after all. Call it a Customers table. –  Jon of All Trades May 17 '13 at 17:31
add comment

1 Answer

up vote 2 down vote accepted

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:

  • Maintain third normal form
  • Maintain referential integrity
  • Maintain the constraint that each account belongs to either a corporation or a natural person.
  • Preserve the ability to retrieve data simply and directly

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 on customer_type when you join account to the account holder. This probably means using UNION and 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 CHECK constraint. 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 OwnerTypeID column). 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.

share|improve this answer
    
My solution #2 to which of your four groups belongs? The "Denormalized Partitioning Attribute Solution" is not quite clear to me.. –  dendini May 20 '13 at 11:31
    
@dendini - You solution number 2 doesn't fit with any of the solutions I outlined. This is because it doesn't fit with the requirement of an account belonging to one legal entity. The way you've defined the primary keys of the intermediate tables, they are many-to-many intersections. If the primary keys were just corporation_id and person_id then you would essentially have the sub-typing solution, except that the super-type table would have been split into two and the foreign key will have been inverted, so people couldn't hold multiple accounts. This kind of defeats the purpose. –  Joel Brown May 20 '13 at 12:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.