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 1234»»»

Complex many to many relationships Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 12, 2013 5:04 PM
Points: 9, Visits: 40
Hi all,

Here is a relatively complex design that I would like to have vetted by experts! :)

Say that you need to track PhoneNumbers for different objects, such as Clients, Staff, Managers, etc. Each object could have many phone numbers associated with them.

The basic approach to many to many would be to create join tables between objects to maintain referrential integrity (such as ClientsPhoneNumbers, StaffPhoneNumbers, etc). We have about 10 different object types for which to track phone numbers, and then the same for addresses, etc. Pretty soon we could have 20+ join tables.

The other approach is to track parent object references on the phone, and differentiate the parents by parent type (i.e. each phone number would track the parent's ID and its type such as Client, Manager, etc.). The problem with this approach is that it does not maintain referrential integrity and you run the risk of having orphaned records in the database.

What are other approaches to this problem?

Thanks everyone!
:)
Post #1446557
Posted Thursday, April 25, 2013 9:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
What might you gain by putting phone numbers into a table separate from the entity (client, agent whatever) to which they belong?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1446559
Posted Thursday, April 25, 2013 9:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 12, 2013 5:04 PM
Points: 9, Visits: 40
Well, first they are not many to many then, and second it constrains the software development due to the inability for code reuse.
Post #1446564
Posted Thursday, April 25, 2013 9:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
Ganga-236835 (4/25/2013)
Well, first they are not many to many then, and second it constrains the software development due to the inability for code reuse.


This doesn't really address the point I raised. If phone number is an attribute of [Person] or [Staff] or whatever, what would you gain by abstracting phone number into a separate table? Surely it can only cost more? Can you raise any argument which might support phone number not being an attribute of [Person]?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1446572
Posted Thursday, April 25, 2013 9:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
I would agree that a many to many is not a the way I would choose here. There is nothing to be gained by this. Yes it is possible that you can have multiple contacts with the same phone number. For this type of thing a small amount of data redundancy is not a big deal.

I understand what you are saying that you want to allow for as many phone numbers for a given person as they want. What I don't understand is how that creates orphans. I would establish that a foreign key with cascading delete. That way if you delete the contact, all of their phone numbers get deleted too.


_______________________________________________________________

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 #1446578
Posted Thursday, April 25, 2013 10:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 393, Visits: 2,684
ChrisM@Work (4/25/2013)
Ganga-236835 (4/25/2013)
Well, first they are not many to many then, and second it constrains the software development due to the inability for code reuse.


This doesn't really address the point I raised. If phone number is an attribute of [Person] or [Staff] or whatever, what would you gain by abstracting phone number into a separate table? Surely it can only cost more? Can you raise any argument which might support phone number not being an attribute of [Person]?


Person can have multiple phone numbers. I've seen designs where the phone numbers are in a different table, keyed by the personid and differentiated by phone type, with the ability to add new phone types.
Post #1446610
Posted Thursday, April 25, 2013 10:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 12, 2013 5:04 PM
Points: 9, Visits: 40
ChrisM@Work (4/25/2013)

This doesn't really address the point I raised. If phone number is an attribute of [Person] or [Staff] or whatever, what would you gain by abstracting phone number into a separate table? Surely it can only cost more? Can you raise any argument which might support phone number not being an attribute of [Person]?


Chris, can you help me understand your approach because the way I understand what you are describing it seems that a record for the [person] would have to exists multiple times in its table in order to track multiple [phone numbers] as the phone attributes are integrated into the person table?

Also, I should point out that the many to many relationship I refer to is not the standard many to many model. In a one to many model, a person exists once and a phone record exists X times. In the model I am describing, the phone number exists once per object of different types (person, manager, staff, etc), but since it is not tied to a specific object type, I have called this the "complex many to many relationship" as I do not know how else to describe this model.

G.
Post #1446611
Posted Thursday, April 25, 2013 10:48 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:23 PM
Points: 20,860, Visits: 32,883
Ganga-236835 (4/25/2013)
ChrisM@Work (4/25/2013)

This doesn't really address the point I raised. If phone number is an attribute of [Person] or [Staff] or whatever, what would you gain by abstracting phone number into a separate table? Surely it can only cost more? Can you raise any argument which might support phone number not being an attribute of [Person]?


Chris, can you help me understand your approach because the way I understand what you are describing it seems that a record for the [person] would have to exists multiple times in its table in order to track multiple [phone numbers] as the phone attributes are integrated into the person table?

Also, I should point out that the many to many relationship I refer to is not the standard many to many model. In a one to many model, a person exists once and a phone record exists X times. In the model I am describing, the phone number exists once per object of different types (person, manager, staff, etc), but since it is not tied to a specific object type, I have called this the "complex many to many relationship" as I do not know how else to describe this model.

G.


So, you want to store a phone number only once. You could have 100 people using the number 800-555-1212 and you want to store this only once in your database, correct?



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 #1446616
Posted Thursday, April 25, 2013 10:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
Ganga-236835 (4/25/2013)

Also, I should point out that the many to many relationship I refer to is not the standard many to many model. In a one to many model, a person exists once and a phone record exists X times. In the model I am describing, the phone number exists once per object of different types (person, manager, staff, etc), but since it is not tied to a specific object type, I have called this the "complex many to many relationship" as I do not know how else to describe this model.

G.


Personally I think this over complicates this greatly. The number of rows sharing the same phone number is likely going to be relatively small. There will be a performance hit to normalize to this point. It also adds a little more complexity which to me seems overkill.


_______________________________________________________________

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 #1446618
Posted Thursday, April 25, 2013 10:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 12, 2013 5:04 PM
Points: 9, Visits: 40
Sean Lange (4/25/2013)
Personally I think this over complicates this greatly. The number of rows sharing the same phone number is likely going to be relatively small. There will be a performance hit to normalize to this point. It also adds a little more complexity which to me seems overkill.


Lynn Pettis (4/25/2013)
So, you want to store a phone number only once. You could have 100 people using the number 800-555-1212 and you want to store this only once in your database, correct?


No, the phone number is unique only to a single object and is not shared. In that sense it is a one to many relationship as the object could own multiple phone numbers.

G.

Btw, thanks everyone for contributing! :)
Post #1446619
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse