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

From: David Cressey <>
Date: Thu, 13 Dec 2007 11:54:55 GMT
Message-ID: <j498j.22444$Bg7.15165_at_trndny07>

"d-42" <> wrote in message
> Hi,
> 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.
> 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

It's not clear to me from reading your post, but it sounds like you are discussing the "generalization-specialization" construct. In particular, businesses and persons are specializations of "addressee", and an adressee has an address.

Generalzation-Specialization (Gen-Spec) has been extensively discussed in modeling literature. For a quick overview do a web search on "generalization specialization relational modeling" . Received on Thu Dec 13 2007 - 12:54:55 CET

Original text of this message