Re: Category Types

> >> Are category types a bad database design methodology? <<
>
> Yes.
..... skipped .....
> Ownership in your example is a relationship and not an attribute, but
> you have no table for it; you thought that the fake class hierarchy
> would be enough.
>
> CREATE TABLE AutoTitles
> (vin CHAR(17) NOT NULL
> REFERENCES Vehicles(vin)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> owner_id INTEGER NOT NULL
> REFERENCES Owners(owner_id)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> ...
> PRIMARY KEY (vin, owner_id));
>
> CREATE TABLE Vehicles
> (vin CHAR(17) NOT NULL PRIMARY KEY,
> ...);
>
> CREATE TABLE Owners
> (owner_id INTEGER NOT NULL PRIMARY KEY,
> legal_status CHAR(1) DEFAULT 'p' NOT NULL
> CHECK (legal_status IN ('p, 'b', 'c'),
> owner_name ...
> );

I have a question to Mr. CELKO.
How do you suggest to keep referential integrity in this schema ?

Design, offered by George R. seems pretty better for this task.

I think, this is one of the problems, which could not be solved efficienlty in modern RDBMS
They does not support foreign constraints like REFERENCES tableA or tableB or tableC.

So, we have only one decision to solve this: create a table with IDs from all 3 tables.
And we have 2 ways than: make this table a super class (our tables will reference it by FKs), or do not make it a super class (just storing IDs from 3 tables and no constraints).

After creating such a table we can be sure, that we reference an existing record (in example above, we reference car owner).

This approach can be made with hope, that in the future RDBMSs will support OR like foreign constraints.

Or should we do not use FKs in this case at all ? Just have 3 tables - Person, Company, Bank and a Car with field owner_id, which has no reference ? It seems not very good for me.

P.S. sorry for my English

WBR, Alexey Kirich Received on Wed Jun 18 2003 - 10:09:25 CEST