Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Design pattern for Phone Expand / Collapse
Author
Message
Posted Friday, August 24, 2012 11:01 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 2,394, Visits: 18,021
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.
Post #1349820
Posted Friday, August 24, 2012 12:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:16 AM
Points: 12,923, Visits: 32,282
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1349863
Posted Friday, August 24, 2012 12:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 13,206, Visits: 12,687
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)
Post #1349874
Posted Friday, August 24, 2012 12:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:43 PM
Points: 3,912, Visits: 8,858
What happens when a location/person has more than one phone?
It's really common.



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1349879
Posted Friday, August 24, 2012 12:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:25 AM
Points: 20,795, Visits: 32,710
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.



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)
Post #1349880
Posted Friday, August 24, 2012 1:36 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 2,394, Visits: 18,021
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...


  Post Attachments 
model1.jpg (96 views, 44.83 KB)
Model2.JPG (96 views, 57.55 KB)
Post #1349900
Posted Friday, August 24, 2012 2:04 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:31 PM
Points: 150, Visits: 954
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.
Post #1349917
Posted Friday, August 24, 2012 2:20 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:43 PM
Points: 3,912, Visits: 8,858
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1349924
Posted Friday, September 21, 2012 6:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:38 AM
Points: 7,851, Visits: 9,600
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
Post #1362587
Posted Friday, September 21, 2012 9:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:17 AM
Points: 31,278, Visits: 15,731
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
Post #1362760
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse