Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
add comment

4 Answers

up vote 1 down vote accepted

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.

share|improve this answer
1  
Pragmatically the unique constraint needs to be on the combination of client ID and extension number. –  Murph Dec 20 '10 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 Dec 20 '10 at 20:36
add comment

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.

share|improve this answer
    
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 Dec 20 '10 at 14:34
    
Read post more carefully - sorry for the misunderstanding. I've edited my answer accordingly. –  Michael K Dec 20 '10 at 14:44
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
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.