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

"d-42" <db.porsche (AT) gmail (DOT) com> wrote

"d-42" <db.porsche (AT) gmail (DOT) com> wrote

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" .

d-42 wrote:

I suggest you shelve that textbook and read another.

On Dec 12, 9:30 pm, d-42 <db.pors... (AT) gmail (DOT) com> wrote:
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 stand-
alone tables, each with their own intersection entity to the single
ADDRESS table, as you describe above.

--Jeff

Thanks. I think that's what I really wanted to know: "How is it
actually normally done?" I could see a number of solutions driven from
the theory, but found most of them felt contrived or over engineered.
I am never going to need to refer to PARTYs, so constructing it just
to let two different and independant entities have addresses
seemed...wrong.

If I was designing a vehicle database, and was tracking cars, trucks,
and motorcycles I could see a VEHICLE supertype, with specialized
subtypes... but for handing the fact that a number of entities in the
system could have an address?

Your confirmation/observation that while conceptually it fits into the
gen-spec or supertype/subtype problem but in practice its not normally
implemented that way was very helpful.

Thanks again.
-Dave

The classic scenario calls for a root class with all the common
attributes and then specialized sub-classes under it. As an example,
let's take the class of Vehicles and find an industry standard
identifier (VIN), and add two mutually exclusive sub-classes, Sport
utility vehicles and sedans ('SUV', 'SED').

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
UNIQUE (vin, vehicle_type),
..);

Notice the overlapping candidate keys. I then use a compound candidate
key (vin, vehicle_type) and a constraint in each sub-class table to
assure that the vehicle_type is locked and agrees with the Vehicles
table. Add some DRI actions and you are done:

CREATE TABLE SUV
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL
CHECK(vehicle_type = 'SUV'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type = 'SED'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

I can continue to build a hierarchy like this. For example, if I had
a Sedans table that broke down into two-door and four-door sedans, I
could a schema like this:

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL
CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE TwoDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '2DR' NOT NULL
CHECK(vehicle_type = '2DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

CREATE TABLE FourDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '4DR' NOT NULL
CHECK(vehicle_type = '4DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans (vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

The idea is to build a chain of identifiers and types in a UNIQUE()
constraint that go up the tree when you use a REFERENCES constraint.
Obviously, you can do variants of this trick to get different class
structures.

If an entity doesn't have to be exclusively one subtype, you play with
the root of the class hierarchy:

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN ('SUV', 'SED')),
PRIMARY KEY (vin, vehicle_type),
..);

Now start hiding all this stuff in VIEWs immediately and add an
INSTEAD OF trigger to those VIEWs.