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.