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

On Dec 12, 9:30 pm, d-42 <db.pors..._at_gmail.com> wrote:
> 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.
> 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

Hi, Dave:

Page 40 of Silverston, et al.'s book _The Data Model Resource Book_? describes a supertype of PARTY with two subtypes, PERSON and ORGANIZATION. This supertype is then linked to an ADDRESS entity via an intersection entity called PARTY_ADDRESS (naturally ;-). Thus you have addresses for people as well as businesses.

In my experience, it's uncommon to see the PARTY supertype implemented as-is. Usually PERSONs and ORGANIZATIONs are implemented as standalone  tables, each with their own intersection entity to the single ADDRESS table, as you describe above.

--Jeff Received on Thu Dec 13 2007 - 20:50:09 CET