Multiple Parent Tables (or Multiple Inheritence, or Arc-Relationships...)

It seems to be a common enough problem, but it doesn't seem to ever be properly addressed in data modelling books, and from reading on the web its clear that other people have had this problem as well as had multiple solutions presented, but its unclear what is actually the "right" answer.

The problem is, suppose you have an entity: Person that has Addresses:

The solution, two tables:

Person { namefirst, namelast, birthdate, personid(pkey) } Addresses { line1, line2, city, state, postcode, country, addressid(pkey), personid(fkey) }

So far, so good. Textbook.

Now suppose you add a new entity Businesses, and they can have people
(with addresses), but they can also have addresses themselves. And
looking through the entities, half a dozen more happen to have addresses too. (Although some only have exactly one, or exactly two, instead of being one->many relationships)

The temptation is clear... we have all these entities that need addresses, and we have an address table that has the schema we need. SHOULD WE USE IT? I've seen people suggest multiple foreign-keys one to each possible parent where all are nullable, and one and only one must be valid.
(arc relationships in oracle, also known as exclusive-or
relationships).

I've seen people suggest 'multiple inheritance' where you make both businesses and people subtypes of a common 'addressable_entity' supertype...

And then you have:
AddressableEntity { addressable_entityid, ... } Addresses {addressid, addressable_entityid, ... }

And Persons and Businesses are considered 'subclass' children of the Addressable_Entity parent.

Person {addressable_entityid, personid,...} Business {addressable_entityid, personid, ...}

I've seen people suggest creating multiple join tables one for each 'parent entity', and then enforcing a reference to addresses to be referenced in exactly one of the join tables. So you have:

Person { personid,...}
Business {businessid,...}
Addresses {addressid, ...}

BusinessAddresses {businessid, addressid} PersonAddresses {personid, addressid}

And of course I've seen people suggest simply ignoring the impulse to put all addresses in one table, and just create multiple child tables:

Person {personid, ...}
PersonAddresses {addressid, personid, line1, line2,...}

Business {businessid, ...}
BusinessAddresses {addressid, businessid, line1, line2, ...}

So what is the best approach? And I guess by that I mean, what would you actually do in practice? And most importantly why would you choose it over the other options?

(And what about those entities with exactly one or exactly two
addresses? How would those fit in?)

And if this has already been discussed to death... please point me to the right spot.

-best regards,
Dave Received on Thu Dec 13 2007 - 06:30:48 CET