6

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?

2
  • Can one phone number be used to contact more than one customer? Do you ever need to search by phone number?
    – A-K
    Nov 12, 2012 at 3:54
  • @A-K I know the comment is old, but for future readers the answer to both of those questions is no.
    – Andy
    Mar 25, 2017 at 13:37

3 Answers 3

6

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.

3
  • 2
    +1 On this for most part. Phone numbers don't change that much for your data set, I imagine, so the benefits of going that extra layer of normalization may be a bit much. Anytime you want to run a report that has a phone number and other data, you now have an extra join happening, too.. The only thing that -may- cause me to change my mind is if there was a chance that the rules you have today could change in the future. Today you can only have 0, 1 or 2 phone numbers and you seem reasonably sure you'll never have more than 2... I've been around enough to see "never" is a strong word. ;-)
    – Mike Walsh
    Nov 11, 2012 at 18:43
  • 1
    @MikeWalsh I see what you're saying about needing more than two at some point in the future. We've had at most two for eight years now, so I don't see it changing, nor has anyone given indication that we should change it. So as a developer, more than two is YAGNI, and if in a few years it turns out we do need more, we can change the schema at that point. As you said, nothing is ever really set in stone. :-)
    – Andy
    Nov 12, 2012 at 13:35
  • 2
    Whether it is acceptable or not depends on the following: Can one phone number be used to contact more than one customer? Do you ever need to search by phone number? Enforcing uniqueness and/or searching is so very much easier if it is normalized. Without knowing the requirements, we do not know for sure which approach is better...
    – A-K
    Nov 12, 2012 at 16:06
5

Over and above the reasoned argument of Mark Storey-Smith and Damir Sudarevic, I would add two points that I think are important to keep in mind.

I never say never (well hardly ever)

My policy about never is that if it has been imagined then it is inevitable at some point. Don't believe it when users tell you that something will never happen. For your purposes that means that you need to think about the likelihood and the seriousness of a change in your design around phone numbers. All design is about making informed compromises. Do what makes sense, taking into consideration the risk involved should requirements change.

Normalization is vital - unless it isn't

Unless you have a really good reason not to, your first instinct should always be to normalize your schema. This is because normalization is a great rule of thumb to keep you from creating big data consistency and code maintenance problems for yourself.

There are reasons to denormalize. One big one is reporting performance when reading static data. Another reason you might consider denormalizing is if the data in question isn't meaningful to your system. What do I mean by that? Data which needs to be interpreted by your system is meaningful. Data that is going along for the ride but isn't read, massaged, or interpreted by your software isn't especially meaningful. Data that isn't meaningful is much less of a risk when it comes to consistency.

For practical purposes in your case, if your system uses phone numbers for an IVR system that is pretty meaningful. If on the other hand the only thing the phone numbers will ever be used for is for a user to read it off a screen once in a blue moon, then maybe you can afford to be a little "sloppy".

2
  • Amen on the Never argument :-) That's where I was going in my comment on Mark's post with my "I've been around enough to see "never" is a strong word." - If there is a chance that the rules of how many and how phone numbers are used will change and you'll ever take more than one - this design becomes a big pain on that day.
    – Mike Walsh
    Nov 12, 2012 at 2:29
  • As I said to Mike, I agree that never doesn't really mean ever. But nothing near term, and if that day comes we need more, we can change the schema then.
    – Andy
    Nov 12, 2012 at 13:37
1

The weird thing is that you are linking those phones to the address, as opposed to customer. So, if the address is known, then phones are stored in one table; otherwise somewhere else.

So if you think that there are two types of phones -- one type belongs to the building and the other to a customer; then your solution is OK.

If you think that a phone belongs to a customer (person or organization) -- then you should probably reconsider.

1
  • The phone numbers linked to a customer are used to contact that customer. Phone numbers linked to an address are used to contact whomever lives at that address, which may not be the customer. The addresses may be used for delivery, in which case we need to contact someone at the address.
    – Andy
    Nov 11, 2012 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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