Database design, inherit

[PEK]

I have a database that looks something like this:

Table: Creature
Fields, CreatureId {PK}, ...

Table: Bug
Fields: BugId {PK}, CreatureId {FK}...

Table: LadyBird
Fields: LadyBirdId {PK}, BugId {FK}...

Every creature in the system is in the Creature-table. If the creature
also is a bug, then it's also in the bug table. And if it's a
LadyBird, it's also in the LadyBird-table.

A problem with the current design is that several bugs could be of the
same creature, and several LadyBirds could be of the same bug which
should not be possible.

So I'm thinking about removing the fields BugId and LadyBirdId, and
use CreatureId (as primary key) instead in the bug and ladybird table.
Would that improve the design of the database? With a new design it's
possible to have a LadyBird that's not a Bug.

I still thinks the new design would be better, but I'm gladly accept
advices before I starts the redesign (which will be quite some work to
do :-/ )

PEK

[--CELKO--]

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_t ype 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_t ype = '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_t ype = '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_t ype 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_t ype = '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_t ype = '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_t ype 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. Faking an OO class model like this will be
slow and a bitch to maintain, but there it is. You might want to try a
relational design instead.

[pekspro]

--CELKO-- wrote:

Now start hiding all this stuff in VIEWs immediately and add an INSTEAD OF trigger to those VIEWs. Faking an OO class model like this will be slow and a bitch to maintain, but there it is. You might want to try a relational design instead.

Thanks for your detailed answer. Adding a "type" key was an clever idea
:-). However, it seems that in some tables (like FourDoor) the type
column will have the same value in every row which I'm not very
comfortable with. But if there is no away around it maybe that's the
way to go.

A relation design may be better, but that's what I'm trying to create
(but sense I think that everything is objects I'm guess that affects my
solution :-). The problem I'm actually trying to solve is something
like this:

I have three tables with animals (say, Bug, Cat and Dog) and three
tables with places (like House, Car and Boat). Every animal could have
(but not always have) an relation every place (like "The cat 12 entered
the house 21 at 21:12"). It's the relation that is the problem. I could
create nine table for every possible solution (but that feels like a
bad design, or?). One generic table would be a more elegant solution. I
see two approaches:

First, inspired from your solution, the tables would look something
like this:
Bug (pk: BugId, AnimalType)
Cat (pk: CatId, AnimalType)
Dog (pk: DogId, AnimalType)
House (pk: HouseId, PlaceType)
Car (pk: CarId, PlaceType)
Boat (pk: BoatId, PlaceType)
AnimalPlace( pk: AnimalId, AnimalType, PlaceId, PlaceType )

The second solution, which I think is better, would require unique id's
as primary keys for the animals and places (so if a cat has id 1,
neither a bug or dog could have id 1). This would remove the type
problem (this is how I thought in the beginning):
Bug (pk: BugId)
Cat (pk: CatId)
Dog (pk: DogId)
House (pk: HouseId)
Car (pk: CarId)
Boat (pk: BoatId)
AnimalPlace( pk: AnimalId, PlaceId, )

Both these solutions has the problem is that I have no idea how to
create the foreign keys in the AnimalPlace table. The AnimalId should
be a foreign key to one the primary keys in the animal tables (BugId,
CatId or DogId). But that is not possible, or? I found this thread with
Google which discussing the problem:

http://groups.google.se/groups?hl=sv...ink.net#link10

A clever man almost solves the problem in the end. But it requires that
all children is created before there parents (in my case, AnimalPlace
should be created before Cat for example).

So how to handle this problem. Is subclass-superclass the way to solve
this?

PEK