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