I saw the recent post about the person questioning his boss's solution (I believe my solution is better than my boss, so should I ignore him?) and decided to ask something similar but not as "clashing" as it sounded in the above link.

To put it simply and more conceptually:
We have a web app that does phone billing for many different clients. The thing is, the person that started the project didn't consider that there are only 9999 possible phone extensions in our design (a bit naive, I know). We have come across a point where different clients's users will have to share the same extension number. In the DB level, the extension number is fixed as unique and the Extensions table has a foreign key to Users.

The easiest solution as we saw was to remove this unique constraint and allow extension numbers to be duplicated because we can still check and validate to see which Client the User belongs to. However, the person that oversees my work believed it would cause future problems if we simply allowed for duplication. He suggested I create an intermediary table that links two tables together, namely UserProfile (which has a foreign key to User and Client) and the Extension table. Since my way seemed easier to do, I created a branch of the project to test if it works fine, which it does. Then I started implementing the other way and came across some complications, system-wide modifications, but I still think it's doable. The person that oversees my work is a sys admin that does some programming. I'm the full-time programmer, though without much experience (still young ;) ). It would be cool if I had some outside perspective on my dilemma?

Thanks a lot guys.

4 Answers 4


This is a bit of an abstract answer, but I would go with the design that most closely models the business. In your case, it seems like in reality, you do have situations where people share extensions, because you are modeling many distinct phone systems. Therefore I would drop the unique constraint. This means that extension is no longer a natural key for a person, but I don't really see that as a problem.

  • 1
    Pragmatically the unique constraint needs to be on the combination of client ID and extension number.
    – Murph
    Commented Dec 20, 2010 at 16:27
  • At times I wish I could give two upvotes. Murph nailed it. Make an unique index on clientID and extention number. That is the unique thingy, so should be an index. If that means redesigning tables, it should be done. If that means it is a lot of work, it means you have a large project on your hands, not that you should work around it.
    – Martijn
    Commented Dec 20, 2010 at 20:36

I think that dimensionally the link table is better, because you don't duplicate data. That extension table will not grow at all, giving more space for the client data. However, that would require changing your queries, and if you're not using prepared statements or PL/SQL, then it would require quite afew changes.

I'd say to make the changes. It will save you time later, when another client has the same issue. I share a phone too, btw - sounds like a common scenario. If you have a good suite of automated tests, it should be easy to find problems associated with the change.

  • Oh you got the first part the other way around. Dropping the unique constraint is a lot easier and doesn't require system-wide changes. As far as DB design goes, he does know a lot more.
    – chiurox
    Commented Dec 20, 2010 at 14:34
  • Read post more carefully - sorry for the misunderstanding. I've edited my answer accordingly.
    – Michael K
    Commented Dec 20, 2010 at 14:44

I would add the Client ID in the Extensions table and create a unique index for (Extension number, Client ID; this should avoid all problems related to completely dropping the unique constraint while making it possible to share the extension number between clients.

There is a bit of redundancy because the Client could be found through the User, too; but I think this is not much of a problem and it allows you to e.g. to remove the connection to the User (i.e. the user has been fired, the extension is currently not used) without running into new problems.


From a database perspective, I would say he is correct. It is better to not duplicate data, and business rules (eg: "Users belonging to the same client can not have the same extension") should be enforced on the back-end.

Just dropping the uniqueness may be the quick and dirty solution, but in the long-run has the possibility (however unlikely) of causing issues.

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.