April 26, 2013 at 7:51 am
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.
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
April 26, 2013 at 7:55 am
If a phone number can only belong to one entity, why are you trying to make it a many to many relationship?
April 26, 2013 at 8:05 am
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
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
April 26, 2013 at 8:06 am
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
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
April 26, 2013 at 8:06 am
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.
April 26, 2013 at 8:07 am
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?
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
April 26, 2013 at 8:09 am
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'...
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
April 26, 2013 at 8:10 am
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'
April 26, 2013 at 8:14 am
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.
April 26, 2013 at 8:25 am
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...
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
April 26, 2013 at 8:31 am
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'
April 26, 2013 at 8:36 am
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.
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
April 26, 2013 at 9:23 am
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.
May 1, 2013 at 9:25 am
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
May 1, 2013 at 11:02 am
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
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply