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 8:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 7,115, Visits: 13,473
Ganga-236835 (4/26/2013)
....

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.

...

G.


Just as a matter of interest, can you list out those parent objects?


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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 7,115, Visits: 13,473
Ganga-236835 (4/26/2013)
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.


Those are 'organisations'...


“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 #1447019
Posted Friday, April 26, 2013 8:10 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
[b]ChrisM@Work (4/26/2013)[/b
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



It is not necessary to query per device type, as only the parent ID and parent type can be supplied to return X amount of records of varying phone types.

SELECT * FROM PhoneNumbers WHERE Parent = 'ManagerUniqueID' AND ParentType = 'Manager'

Post #1447023
Posted Friday, April 26, 2013 8:14 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/26/2013)
Those are 'organisations'...


Well, not quite. There could be multiple regional offices for "Organizations", with each one having their own unique set of phone numbers. Again, for the purposes of discussions, let's assume that abstracting to a higher level is not possible.

Or rather, if you want to assume that it is possible, that is fine too. You can proceed on the notion that you can abstract it up to 5 higher level objects which could own phone numbers.

The problem still remains as to how to create the "complex many to many" relationships of phone numbers to each of these 5 higher level objects.

G.
Post #1447025
Posted Friday, April 26, 2013 8:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 7,115, Visits: 13,473
Ganga-236835 (4/26/2013)
ChrisM@Work (4/26/2013)
Those are 'organisations'...


Well, not quite. There could be multiple regional offices for "Organizations", with each one having their own unique set of phone numbers. Again, for the purposes of discussions, let's assume that abstracting to a higher level is not possible.

Or rather, if you want to assume that it is possible, that is fine too. You can proceed on the notion that you can abstract it up to 5 higher level objects which could own phone numbers.

The problem still remains as to how to create the "complex many to many" relationships of phone numbers to each of these 5 higher level objects.

G.


You're designing this system, it's not too late to design it properly. You should read Tom's excellent normalisation series of papers. I put links to them in my reply to him. Better still would be some more comment from him...


“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 #1447029
Posted Friday, April 26, 2013 8:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 354, Visits: 2,238
Ganga-236835 (4/26/2013)
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.


Thats sort of what I was discussing, for instance you'd have an office, then an office_phone_number table with [office_id], [office_phone_type],[office_phone_number], a vendor_phone_table with [vendor_id], [vendor_phone_type],[vendor_phone_number] etc...

An alternative would be to have an abstract [contact] table, like [contact_id], [contact_type] which could be person, office, vendor, etc... then you'd be replacing different phone tables with a single phone table, different entities like person, office, vendor with a single contact table. I've seen at least one system that the [person] table actually performed this role, with multiple linked rows in an address table that had address types, etc, and multiple linked rows in a phone table with phone types, and there was no requirement that the person table had to be limitted to storing rows that described an actual person, for instance, organizations and businesses would have a row describing them in the person table, and a [person_type] column with choices like 'person', 'organization', 'vendor' etc...

edit: removed extra 'e' in 'different'
Post #1447033
Posted Friday, April 26, 2013 8:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 7,115, Visits: 13,473
Ganga-236835 (4/26/2013)
ChrisM@Work (4/26/2013)
Those are 'organisations'...


Well, not quite. There could be multiple regional offices for "Organizations", with each one having their own unique set of phone numbers. ...
G.


Those are 'rows' in the 'organisations' table. You have an attribute in the table which describes the hierarchy.


“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 #1447037
Posted Friday, April 26, 2013 9:23 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:07 AM
Points: 22,993, Visits: 31,473
I'm not going to ask you for the DDL for the tables, or sample data, or expected results. I would like to see and ERD with the tables (including keys and data elements) so we can see how these tables relate to each other and perhaps show you another way to accomplish your task at hand that may not be as complex.



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 #1447061
Posted Wednesday, May 1, 2013 9:25 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 8,551, Visits: 9,040
ChrisM@Work (4/26/2013)

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.
Actually First Name, Middle Name, Last Name, and Called Name - four columns not three - seems to be pretty column, sometimes "Other Names" instead of "Middle Name"; the Called Name is becoming common because companies are slowly discovering that calling someone by a first name which they never use will irritate them and irritating one's customers is not a good thing; Other Names becuase some people have more than one middle name. These name columns can be nullable (except Last Name) but don't need to be since a zero length string works just fine.

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!
Anyone who wants to carry out that normalisation and isn't stupid must have a very large number of people in their database. And probably assumes that no-one ever changes a name. And probably hasn't worked out that doing that increases the risk of data entry errors unless you ensure that the GUI doesn't present a list of names to choose from. There may even be a problem in determining when two names are the same - after all, Alan and Ailean are the same name, aren't they? And Ian and Iain? And Colin and Cailean? And Alasdair and Alastair? And Elizabeth and Betty? And Jack and John?

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.

I don't know what people do in databases generally, but I do know that it's not extremely rare for people to have more than 3 phones. Now I only have 4 (UK home, Spain home, UK mobile, Spain mobile) but a few years back I had 8: UK Home, UK mobile, London office switchboard, London office direct line, Spain home, Spain mobile, India mobile, India office. Most people in the firm had 4 phones or more. We didn't keep the numbers in a database though.

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.

I tend to agree that a three number solution, or maybe a four number solution to cater for the case where staff are required to have a work mobile but mustn't use it for non-work calls so have separate work mobile and personal mobile, will cover the vast majority of cases.


Tom
Post #1448477
Posted Wednesday, May 1, 2013 11:02 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 8,551, Visits: 9,040
Ganga-236835 (4/26/2013)


It is not necessary to query per device type, as only the parent ID and parent type can be supplied to return X amount of records of varying phone types.

SELECT * FROM PhoneNumbers WHERE Parent = 'ManagerUniqueID' AND ParentType = 'Manager'


This is pretty horrible from a relational model point of view, and also from the point of view of having the schema enforce the business rules through its structure (ie normalisation).

One way to do this properly is to have a separate link table for each parent type, so for example you would have
CREATE TABLE ManagerPhoneLink (
ManagerID int not null references Managers(ManagerID)
, PhoneID int not null references PhoneNumbers(PhoneID)
, Constraint ManagerPhone Primary Key (ManagerID,PhoneID)
) ;

and another one like that for each kind of parent you have. That way there's no need for a ParentType at all.

There's an alternative approach which you keep on saying you want to treat as not possible; but you already have invented the required abstraction, Parent so I don't understand the refusal to countenance a table for it. This way you have a Parent table and a ParentTypes table which look like
CREATE TABLE ParentTypes (
ParentTypeID int primary key,
ParentTypeName varchar(48) not null unique
) ;
CREATE TABLE Parents(
ParentID int primary key
, ParentTypeID int not null references ParentTypes (ParentTypeID)
) ;
CREATE TABLE ParentPhoneLink (
ParentID int not null references Parents(ParentID)
, PhoneID int not null references PhoneNumbers(PhoneID)
, Constraint ParentPhone Primary Key (ParentID,PhoneID)
) ;
-- and individual tables for each parent typelike
CREATE TABLE Managers (
ParentID int primary key references Parents(ParentID)
-- and columns for all the rest of a manager's attributes
) ;

This of course is the way that pretty well everyone has been telling you is best, and I agree with them. It is of course ridiculous to ask us, as you have several times, to assume that an abstraction can't be discovered which you have already invented and assigned an attribute(ParentType) or to assume that it is impossible to have a table representing the instances of this abstraction.
As to the height of some putative tower of abstractions, that is of course up to you. You can do it with the individual kinds of parent at the base level and teh single Parent abstraction above it. If you want to you can invent intermediate layers - as many as you like. The problem you have asked for guidance on requires only the top and bottom levels. Adding extra levels in beteen is a non-trivial excercise, and it may not be a useful one - or you may have proposed 5 levels as a limit because you can see uses in some cases for 3 levels above the bottom and below the top (presumably the depth of the tree doesn't need to be uniform once you introduce intermediate levels) in which case the excercise may be useful.


Tom
Post #1448528
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse