SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Complex many to many relationships


Complex many to many relationships

Author
Message
Ganga-236835
Ganga-236835
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 40
Hi all,

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

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!
Smile
ChrisM@Work
ChrisM@Work
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: 16174 Visits: 19543
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
Ganga-236835
Ganga-236835
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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.
ChrisM@Work
ChrisM@Work
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: 16174 Visits: 19543
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25994 Visits: 17528
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 Modens 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)
patrickmcginnis59 10839
patrickmcginnis59 10839
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

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

to properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Ganga-236835
Ganga-236835
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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.
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39201 Visits: 38529
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?

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)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25994 Visits: 17528
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 Modens 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)
Ganga-236835
Ganga-236835
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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! Smile
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