Storing Phone Numbers – Proper design


First off, I'm not a DBA, I'm a software engineer and have been building applications which are database backed for my entire career. One of the things I remember (maybe incorrectly) is that when designing the ERD that you take the real world into account as it serves as the context for your design.

I have a situation where we have the concept of a customer, who can have two and only two phone numbers. A customer can have zero to many addresses as well, and each address can have only two phone numbers (and a flag indicating if the number is a mobile number). The numbers on an address would only be used to contact someone at that particular address, while the numbers for the customer are used to contact the customer (who's address may not be in the system).

The design I came up with was to have a Phone1 Phone2 as columns on the customer table as well as on the address table. I've had others suggest that this isn't a good design and that I should have created a PhoneNumber table instead (I'm not sure how they were suggesting I relate the numbers to the other records).

Certainly I can see that being valid as well, but I either still need to have Phone1Id and Phone2Id in the customer and address table, or have something in the Phone table that tells me which record owns the number. The issue I have though is that of course makes the appliction logic more complex since I now need to add, remove or update the record in the phone table instead of just blanking or nulling the value in the respective tables.

Is my design also acceptable? Is one or the other preferable? Or are both just as valid?

Best Answer

The anti-null brigade would insist you normalize further to create the suggested PhoneNumber table. The practical folk would consider your original design perfectly acceptable. I'm in the latter camp.

Normalize until it hurts, de-normalize until it works.