Database design efficiency with 1 to many relationships limited 1 to 3

Question

This is in mysql, but its a database design issue. If you have a one to many relationship, like a bank customer to bank-accounts, typically you would have the table that records the bank-account information have a foreign key that keeps track of the relationship between account and customer. Now this follows the 3rd normal form thing and is a widely accepted way of doing it.

Now lets say that you are going to limit a user to only having 3 accounts. The current database implementation will support this and nothing would need to change. But another way to do this would have 3 coloms in the account table that have the id of the 3 respective accounts in them. By the way this violates 1st normal form of db design.

The question is what would be the advantage and disadvantages of having the user account relationship recored in this way over the traditional?

Update

Unfortunately I am not in-charge of the db design. When I saw a similar relationship implemented in our db, I asked my boss, the db designer, why he choose to do it that way. I didn't really get a strait answer, or an answer I understood to have logical reasons backing it up. "This is a very common relationship when you work with databases allot, and that is just how you do it." I asked for more clarification. ... Didn't get me anywhere and made him defensive.

Thank you all so much for these post, I couldn't really find any books that even talked about doing something like this. I found lots of books tell me to do it the right way but not many giving an example this broken and then explaining why it will suck.

Answer 1

Advantage: greater performance, no joins to relationship-table needed

Disadvantage: violates 1st normal form (but may be violated for grater performance)

it's up to you ;)

Answer 2

Well first, you will have lots of blank fields for records for customers that have less than three accounts.

Adding a fourth or more accounts will require adding columns to the table, which again will result in more blank fields for each record.

Secondly, it'll be easier to query the data (for things like the total number of accounts) if they are stored in a separate table.

The reason we use separate tables for 1 to N relationships is that it will save you headaches like these down the line.

Answer 3

Advantages:

• Faster than normal form (by how much?)
• Simpler queries for basic operations (no joins)
• Slightly easier to impose maximum limits

Disadvantages:

• Extensibility
• Added business logic (What if a customer closes their first account? Shift the others?)
• Wasted space (considerable if the average user doesn't have 3 accounts)
• Aggregate metrics more difficult to obtain (i.e.: exact total number of accounts)
• You can't claim your DB is normalized

Both options are valid given the requirements. If possible, benchmark the difference and see if the performance, and calculate the storage difference to see if it's worth it for your deployment.

However, I would likely opt to use a trigger to impose the account limit since that would provide the easiest maintainability, not waste disk space and future developers won't wonder why I can't even get 1NF right.

Answer 4

Now lets say that you are going to limit a user to only having 3 accounts.

This involves a magic number that is subject to change.

Don't believe anyone who says there's a "limit". Today's absolute maximum is tomorrow's minimum.

Do not participate in enforcing this silliness in the database. All "limits" are nothing more than "typical values for the current application" and will change.

Use normal 1-to-many with normal foreign keys and ignore the magic number "3".

Answer 5

The 3 column approach suffers if there's a business rule change (IE: users can now have FOUR accounts, ah ah ah...). This would require an ALTER table statement vs INSERT, and all supporting logic would have to be revisited to accommodate the new column(s) -- very expensive from a development perspective. Additionally, databases do have a column limit, when 3NF wouldn't be affected by.

Answer 6

The biggest problem is that your queries become more complicated. Say you want to find all accounts with balance over $10,000 with the owner. In a normalized DB, that would be something like:

select firstname, lastname, accountnumber, balance
from account
join customeraccount using (accountnumber)
join customer using (customernumber)
where balance>10000

But with three accountnumber fields, it becomes

select firstname, latname, accountnumber, balance
from account
join customer on customer.accountnumber1=account.accountnumber
  or customer.accountnumber2=account.accountnumber
  or customer.accountnumber3=account.accountnumber
where balance>10000

Every query that joins Account to Customer now gets more complicated like this.

Sooner or later, someone will write a query that fails to check accountnumber3, or he tries to make the three tests by copy-and-paste and after copying accountnumber1 two times he forgets to change one of them. It's an error that's easy to overlook when reading a query. If you mess up one of the three compares, the program will work for all customers who have only two accounts but fail for customers who have three. This is the sort of problem that can easily slip through testing.

You now have to think through exactly how the joins work when the same customer has multiple accounts. If a customer has two qualifying accounts in some query, do you want him to show up once or twice?

You probably need to index on the account number field in customer. Now you need three indexes instead of one. More overhead for the database.

Are you sure that the maximum will never ever change? Because if it ever does, now every one of those queries that check the three slots will have to be changed to check four slots. That could be a ton of work.

What do you gain in exchange for all this pain? Automatic enforcement of the max-3 limit. One less table. You might possibly get better performance on some queries because there is one less table to be joined. Then again you might not get better performance, depending on many details of the inner workings of the database engine and the actual data.

All told, I would say it is almost certainly not worth doing. Stick with the normalized database.

I speak from experience. I did something very similar to this once. We had a database where we had to record three types of "managers" for each book our organization published (#1 in charge of budgeting and administration, #2 in charge of distribution, and #3 in charge of contents (i.e. the editor). As the three were different, I created three separate points. Huge mistake. I would have been much better off to create a book-manager table with a type code and enforce only one of each type with triggers or code. The queries would have been way simpler. (Experience enables you to make good decisions. Experience is gained by making bad decisions.)

Answer 7

Possible disadvantages of splitting account numbers over multiple columns:

You would have to repeat query logic and any other code that references account number.

You would have to use some placeholder instead of an account number if there were fewer than three.

It's not automatically a violation of First Normal Form because 1NF is nothing more than the definition of a relation itself. It's just not a very practical design (DRY principle).

Answer 8

sounds like your all drowning in a glass of water!.. why not just add a counter column in each detail account table and if you try to add a new account row, check the counter?