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 Friday, April 26, 2013 1:20 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 8,288, Visits: 8,739
ChrisM@Work (4/25/2013)
What might you gain by putting phone numbers into a table separate from the entity (client, agent whatever) to which they belong?

Maybe the entity owning the phone number has 35 phone numbers; maybe he has only one. What's the maximum number? Do I want to have that many columns when most entities have only 3 or fewer? Should I really have all those nasty nullable columns beibg used to create a table with in effect a number of coulmns which varies from row to row - the classical use of nullability to conceal what is really a clear violation of 1NF.
Of course if each person has exacly 1 staff phne number, exactly one personal fixed phone number, exactly one mobile fixed phone number, and so onm - exactly one phone number in each possible category of phone numbers - then it's perfectly reasonable to put teh numbers in the persons table. Otherwise, you have to look at what the actual data is like - vast munbers of nullable col;umns are not a good idea, but a small number could be OK.

Anyway, the classical solution is the one mentioned by Patrick McG: a table with
person_id, phone_id, connection_type (connection type being values like "staff phone number", "personal mobile", "manager's number", "secretary's number", and so on. Maybe the lily could be gilded by putting the connection types in a separate table and giving them ids and having the join table refer to them by id. Some would say that that's both the underlying model for which including the type in the join table is a kludge and that this is why it's called a complex join table - because it's a three way join, not a two way join - but I prefer the "kludge" unless the types are both very very many and individually quite big; I see no problem with not bothering to have a table for an entity which has no attribute other than its identity, and consider that introducing an extra table to create an imaginary attribute (the surrogate key) would be a silly violation of Occam's principle unless it was needed to save space.



Tom
Post #1446824
Posted Friday, April 26, 2013 3:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
L' Eomot Inversé (4/26/2013)
ChrisM@Work (4/25/2013)
What might you gain by putting phone numbers into a table separate from the entity (client, agent whatever) to which they belong?

Maybe the entity owning the phone number has 35 phone numbers; maybe he has only one. What's the maximum number?

Good question.


Do I want to have that many columns when most entities have only 3 or fewer?Should I really have all those nasty nullable columns beibg used to create a table with in effect a number of coulmns which varies from row to row - the classical use of nullability to conceal what is really a clear violation of 1NF.

Show me a person table which doesn’t have columns firstname, middlenames, lastname. However, not every person has or is willing to reveal their middle name(s). Some are unwilling to reveal their first name(s). It’s a really good fit for most cases though, and a few nulls are more acceptable than the cost and complexity of normalising out the separate name elements.
Few persons have a unique string as one of those name elements, so there’s a valid argument for taking this one step further and having names – perhaps all names ever written down – in a lookup with a many to many link between person and names. It might take up a little less disk space (if it included only the names in use in your persons table) but wouldn’t it be a PITA to interrogate, never mind maintain!


Of course if each person has exacly 1 staff phne number, exactly one personal fixed phone number, exactly one mobile fixed phone number, and so onm - exactly one phone number in each possible category of phone numbers - then it's perfectly reasonable to put teh numbers in the persons table. Otherwise, you have to look at what the actual data is like - vast munbers of nullable col;umns are not a good idea, but a small number could be OK.

I believe this is the commonest solution, at least in my experience. Phone usage / ownership in the West and elsewhere is consistent enough that homephone, workphone and mobilephone fit most cases very well. The devices are not exactly equivalent primarily because of the attribute of usage pattern – they’re not the same as phone1, phone2 and phone3.


Anyway, the classical solution is the one mentioned by Patrick McG: a table with
person_id, phone_id, connection_type (connection type being values like "staff phone number", "personal mobile", "manager's number", "secretary's number", and so on.

I’ve seen this only once in a fair few years of db work and IIRC it was barely worth the effort (of maintaining the extra table) because there were very very few edge cases where a person wished to have more than one ‘number’ in any one category. That doesn’t mean I disagree with it – back to the first paragraph, you may be working for a business where persons routinely own or use more phone numbers than the bogstandard three. But for the vast majority of cases, three covers most bases, just like three names.


Maybe the lily could be gilded by putting the connection types in a separate table and giving them ids and having the join table refer to them by id. Some would say that that's both the underlying model for which including the type in the join table is a kludge and that this is why it's called a complex join table - because it's a three way join, not a two way join - but I prefer the "kludge" unless the types are both very very many and individually quite big; I see no problem with not bothering to have a table for an entity which has no attribute other than its identity, and consider that introducing an extra table to create an imaginary attribute (the surrogate key) would be a silly violation of Occam's principle unless it was needed to save space.


Thank you for stopping by Tom, I was hoping you would, as ssc’s authority on normalisation:

First Normal Form
Second Normal Form
Third Normal Form




“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 #1446871
Posted Friday, April 26, 2013 7:30 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
Although I agree in principle that most common scenario regarding phone numbers is that there is one per person, in the system that I am designing that is just not the case. The [Phone] table stores office, home, mobile, mobile2, fax numbers for the majority of parent objects, but not all as some could have no phone numbers.

Adding these columns to each parent's object just does not make sense. There is no supporting case, at least in this system, where it would make sense to add all these extra nullable columns to each parent object's table. Abstracting it to the separate table is the most logical route.

The challenge again then is mapping these phone numbers to variying parent objects. The solution I am leaning towards is adding the parent id and parent type columns to each phone number so that querying id "123abc" for parent "Manager" returns X amount of phone numbers for that particular business object owner. Again, the issue is that these phone numbers could remain orphaned as there is no foreign key constraint on the parent ID column as that ID could be mapped to many different business object owners.

G.
Post #1446984
Posted Friday, April 26, 2013 7:44 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:10 AM
Points: 551, Visits: 3,165
is it possible, or even considered OK, to put all your different Members tables (Clients, Staff, Managers) into one table called 'Clients', and have a Foreign Key to a Table called ClientType which would detail whether they were Internal Staff, External Contacts etc.?

then your Phone Table would only have to link to that one Clients Table.
Post #1446999
Posted Friday, April 26, 2013 7:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:26 PM
Points: 280, Visits: 1,782
Ganga-236835 (4/26/2013)
Although I agree in principle that most common scenario regarding phone numbers is that there is one per person, in the system that I am designing that is just not the case. The [Phone] table stores office, home, mobile, mobile2, fax numbers for the majority of parent objects, but not all as some could have no phone numbers.

Adding these columns to each parent's object just does not make sense. There is no supporting case, at least in this system, where it would make sense to add all these extra nullable columns to each parent object's table. Abstracting it to the separate table is the most logical route.

The challenge again then is mapping these phone numbers to variying parent objects. The solution I am leaning towards is adding the parent id and parent type columns to each phone number so that querying id "123abc" for parent "Manager" returns X amount of phone numbers for that particular business object owner. Again, the issue is that these phone numbers could remain orphaned as there is no foreign key constraint on the parent ID column as that ID could be mapped to many different business object owners.

G.


This is an interesting case, so I'm guessing that it really depends on how important the functionality of the foreign key constraint is for your needed functionality. If it is really important, then I would lean toward having a phone table for each parent table that needs to have zero or more phone numbers linked to it. If you wanted to report on all phone numbers, you could use a union query of some sort.
Post #1447001
Posted Friday, April 26, 2013 7:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
davidandrews13 (4/26/2013)
is it possible, or even considered OK, to put all your different Members tables (Clients, Staff, Managers) into one table called 'Clients', and have a Foreign Key to a Table called ClientType which would detail whether they were Internal Staff, External Contacts etc.?

then your Phone Table would only have to link to that one Clients Table.


Client, Staff, Manager are attributes of an entity 'Person' so I'd agree with this suggestion.


“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 #1447005
Posted Friday, April 26, 2013 7:55 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 22,510, Visits: 30,231
If a phone number can only belong to one entity, why are you trying to make it a many to many relationship?



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 #1447010
Posted Friday, April 26, 2013 8:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
Ganga-236835 (4/26/2013)
Although I agree in principle that most common scenario regarding phone numbers is that there is one per person, in the system that I am designing that is just not the case. The [Phone] table stores office, home, mobile, mobile2, fax numbers for the majority of parent objects, but not all as some could have no phone numbers.

Adding these columns to each parent's object just does not make sense. There is no supporting case, at least in this system, where it would make sense to add all these extra nullable columns to each parent object's table. Abstracting it to the separate table is the most logical route.

The challenge again then is mapping these phone numbers to variying parent objects. The solution I am leaning towards is adding the parent id and parent type columns to each phone number so that querying id "123abc" for parent "Manager" returns X amount of phone numbers for that particular business object owner. Again, the issue is that these phone numbers could remain orphaned as there is no foreign key constraint on the parent ID column as that ID could be mapped to many different business object owners.

G.


Look at the work you will have to do when you want to bring each of those phone numbers into line with a parent row:

SELECT p.*, 
[mobile 1] = f1.PhoneNum,
[mobile 2] = f2.PhoneNum,
[home] = f3.PhoneNum,
[office 1] = f4.PhoneNum,
[office 2] = f5.PhoneNum
FROM Persons p
LEFT JOIN PhoneNumbers f1
ON f1.PersonID = p.PersonID AND f1.DeviceType = 'mobile 1'
LEFT JOIN PhoneNumbers f2
ON f2.PersonID = p.PersonID AND f2.DeviceType = 'mobile 2'
LEFT JOIN PhoneNumbers f3
ON f3.PersonID = p.PersonID AND f3.DeviceType = 'home'
LEFT JOIN PhoneNumbers f4
ON f4.PersonID = p.PersonID AND f4.DeviceType = 'office 1'
LEFT JOIN PhoneNumbers f5
ON f5.PersonID = p.PersonID AND f5.DeviceType = 'office 2'


SELECT p.*, x.*
FROM Persons p
CROSS APPLY (
SELECT
[mobile 1] = MAX(CASE WHEN f1.DeviceType = 'mobile 1' THEN PhoneNum END),
[mobile 2] = MAX(CASE WHEN f1.DeviceType = 'mobile 2' THEN PhoneNum END),
[home] = MAX(CASE WHEN f1.DeviceType = 'home' THEN PhoneNum END),
[office 1] = MAX(CASE WHEN f1.DeviceType = 'office 1' THEN PhoneNum END),
[office 2] = MAX(CASE WHEN f1.DeviceType = 'office 2' THEN PhoneNum END)
FROM PhoneNumbers f1
WHERE f1.PersonID = p.PersonID
) x



“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 #1447014
Posted Friday, April 26, 2013 8:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
Lynn Pettis (4/26/2013)
If a phone number can only belong to one entity, why are you trying to make it a many to many relationship?


+1


“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 #1447015
Posted Friday, April 26, 2013 8:06 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
patrickmcginnis59 10839 (4/26/2013)
This is an interesting case, so I'm guessing that it really depends on how important the functionality of the foreign key constraint is for your needed functionality. If it is really important, then I would lean toward having a phone table for each parent table that needs to have zero or more phone numbers linked to it. If you wanted to report on all phone numbers, you could use a union query of some sort.


There are other object types that cannot be derived from [Person], such as Offices, Vendors, Trustees, etc. which still own [Phone] numbers. For the purposes of this discussion, let's assume that it is not an option to abstract these business objects to a higher level and bind phone numbers at that level.

G.
Post #1447016
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse