Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Design pattern for Phone


Design pattern for Phone

Author
Message
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2438 Visits: 18649
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.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38958
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!

Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16566 Visits: 17016
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18130
What happens when a location/person has more than one phone?
It's really common.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24198 Visits: 37962
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2438 Visits: 18649
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...
Attachments
model1.jpg (96 views, 44.00 KB)
Model2.JPG (96 views, 57.00 KB)
Bill Talada
Bill Talada
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 1813
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.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18130
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 C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10707 Visits: 12008
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

Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36111 Visits: 18742
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search