Design pattern for Phone
-
I'm considering a change to our current design and I'm curious if anyone has advice or similar experience they can share. I'm sure there is a name for the pattern, but I don't know what it is. We have a Phone table and it is referenced by several other tables - locations have phones, people have phones, and there are a couple other objects that have phones as well. Our initial design had a single phone table and many-to-many join tables between phone and the owning object (e.g. LocationPhone, PersonPhone, etc.). That seems to be the correct "normalized" design, but I'm seeing now that we don't plan to have phones shared among locations and persons, so having them together doesn't give us much benefit. In fact, having them separated would remove ambiguity about what "type" the phone was for (is this number for a location or a person?), make smaller tables (faster queries), and allow us to add different fields that were unique to phones at a location if we needed to in the future. Is this a good or bad pattern? It "feels" wrong - everything should be normalized right? But logically it seems like in this case I'm not getting any benefit from having it all together and the ambiguity and performance makes me lean toward separating them out.
This pattern plays out in a couple other places, maybe 3-4 other tables hooked up similar to how phones are done.
-
i see your point Chad;
I'd consider removing the table as well;'
for me, a phone number is an attribute of a person/location; so the attribute should stay with it's containers.
you could always replace the Current Phone table with a view that gathers the attributes together, but I agree it makes more sense to change it.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I tend to agree with Lowell on this one though. A phone number is rather meaningless by itself. It only gains relevance when you know what it belongs to. I usually put phone number as a column alongside whatever entity it belongs to.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
What happens when a location/person has more than one phone?
It's really common.
-
I have worked with systems where the phone number was broken out to a separate table. The table was tied to a specific individual and the numbers had attributes such as primary (first number to be called) along with if it was a cell (mobile), work, home, even message number (someone that would take a message for a person).
Saw people who had multiple home and/or work and/or cell numbers as well.
-
Thanks Luis - I do plan to allow multiple phones, I've created an image showing the two models I'm considering and I think it is easier to see graphically than decipher my text description, wish I had thought of that first! Granted, the tables have much more info than shown here, but it is sufficient to show the relationships.
This first model is the current structure:
What I don't like is that you can't look at a phone and tell whether it is for the person, location or something else - you have to join and see if it exists in one of the other tables. In addition, the phone table will get large since it has all of them in one spot, and we don't take advantage of having them together since they are not shared (e.g. using the same phone row for both a location and person). Of course, it assumes that all the objects that use phone have exactly the same attributes, which might be true, but might not as well.
This second is the one that I think sounds better:
Lynn - we do have a "type" as well - currently it's hung off the join table since the valid types for locations are different than that for persons,that would change with the new model. Here I show three different entities (person, location, and "another entity"), and this same pattern would apply for addresses, emails, etc.
Thanks,
Chad
EDITED: Added inline references to the images now that I know where they were uploaded to...
-
You pose an interesting question. Are phones central to the application? Should a PersonPhone be grouped with a LocationPhone? Is a phone somewhat of an "attribute" of a person? Just because person phone and location phone share the same word "phone" should they be considered the same thing? Do person phones ever have extensions or "working hours"?
My preference is to not join different tables into one unless they are generally searched together. I'd like to see how everyone chimes in on this problem.
-
I might be wrong, but you could have a column called PhoneEntity so you can know which table uses that phone.
Your "single table model" suggests a phone can be owned by more than one person and (the following you said it happened) a person can have more than one phone.
The design relies on many things and the most common (and correct) answer is "it depends".
The "multiple table model" could generate some duplicates and could multiply your work trying to administer the tables. Other than that, it could be better for performance.
I would like to know what others more experienced have to say. This could be used for addresses as well.
-
Luis Cazares (8/24/2012)
The design relies on many things and the most common (and correct) answer is "it depends".That seems to me to be the most anyone could say without more gen on the business requirement.
Tom
-
I like your second idea, personally. If you need all phones, use a UNION.
One side note: please allow people to change/alter the phone types. I hate on the iPhone that I am limited to their settings of "home", "work", "iPhone", "Mobile", etc. There are a few other differentiators I'd like to add for certain people/groups.
-
Thanks Tom and Steve (and belatedly, Bill). We are migrating to the new model, and hope that we will see the benefits and avoid any unseen pitfalls. I'll let you know how it shakes out and if we run in to anything that makes the model unfeasible.
Steve - our phones might be different, but when you are in the edit mode for a contact, click on the label for one of the phone numbers (e.g. the word "mobile"). I get a long list of options and at the bottom is "Add Custom Label" - from there I can create my own label for my contact.
For our app we are going to go with a generic "Phone 1", "Phone 2", etc. up to a "Phone 7" and store each in it's own column in the table. That way we don't have to worry about types and everyone can choose for themselves what "Phone 1" means without having to define static types. Ok, just kidding we're really not doing that, I couldn't resist. :-D:-P:-D
Thanks!
Chad
-
Chad Crawford (9/21/2012)
For our app we are going to go with a generic "Phone 1", "Phone 2", etc. up to a "Phone 7" and store each in it's own column in the table. That way we don't have to worry about types and everyone can choose for themselves what "Phone 1" means without having to define static types. Ok, just kidding we're really not doing that, I couldn't resist. :-D:-P:-DThanks!
Chad
Why wouldn't you normalize the table?
-
Luis Cazares (9/21/2012)
Chad Crawford (9/21/2012)
For our app we are going to go with a generic "Phone 1", "Phone 2", etc. up to a "Phone 7" and store each in it's own column in the table. That way we don't have to worry about types and everyone can choose for themselves what "Phone 1" means without having to define static types. Ok, just kidding we're really not doing that, I couldn't resist. :-D:-P:-DThanks!
Chad
Why wouldn't you normalize the table?
We did. I had a twitch of humor while reading Steve's comment and tried to think of the worst design possible. I did add that we are not really doing that, sorry that it didn't carry through in the post.
-
Chad Crawford (9/21/2012)
Luis Cazares (9/21/2012)
Chad Crawford (9/21/2012)
For our app we are going to go with a generic "Phone 1", "Phone 2", etc. up to a "Phone 7" and store each in it's own column in the table. That way we don't have to worry about types and everyone can choose for themselves what "Phone 1" means without having to define static types. Ok, just kidding we're really not doing that, I couldn't resist. :-D:-P:-DThanks!
Chad
Why wouldn't you normalize the table?
We did. I had a twitch of humor while reading Steve's comment and tried to think of the worst design possible. I did add that we are not really doing that, sorry that it didn't carry through in the post.
That's what happens when you don't read the last sentence 😀
I was in shock and I missed it. The worst part is that I'm sure there are tables designed like that somewhere in the world.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply