On the contrary, the Suppliers and the Clients tables would each have an fkAddress to the address table. i.e. the Address table is PARENT to them. IMO it would be daft to do it the way you said. What if later you wanted to add, say, an Agents table? Then you'd have to add an fkAgents to the Address table. One or two of these refs may well be redundant.
I used to work for a large bank and their corporate db had one address table. Now take the example where several members of one family all worked for the bank. Each of their Employee recs would have an fkAddress to the Address table.
So I take it you ARE talking generic parent-child here, and not building up a "family tree" dbc?
>This is different. What if I have two parent tables to an Addresses table; say Clients and Suppliers?
>
>So the Addresses table would have an fkClient and an fkSupplier...
>
>>If each child definitely has 2 parents then I see no difference between this and, say, somebody having 2 addresses - home address, work address, say (on a mega-normalised dbc where the addresses are kept in a separate table). If not then you have redundancy.
>>
>>In the latter case, and especially where a parent can be parent to >1 child, you'd want a BOM 1-m-1 relationship
>>
>>PARENT
>>pkParent
>> |
>>PARENTCHILD
>>fkParent
>>fkChild
>> |
>>CHILD
>>pkChild
>>
>>Then a parent can have as many children as they want and vice-versa
>>
>>HTH
>>
>>Terry
>>
>>
>>>I have a situation where I have a child table that will be a child to two different parent tables. It's kind of like this:
>>>
>>>
>>>
>>>PARENT 1 PARENT 2
>>>pkParent1 pkParent2
>>> | |
>>>
>>> |
>>> CHILD
>>> pkChild
>>> fkParent1
>>> fkParent2
>>>
>>>
>>>
>>>My question is...Is this good design? Is it commly acceptable to have a child with two foreign keys pointing to differnt parent tables when one or the other fk value will be null?
- Whoever said that women are the weaker sex never tried to wrest the bedclothes off one in the middle of the night
- Worry is the interest you pay, in advance, for a loan that you may never need to take out.