CODEWITHSUNDEEP
databaseinheritance
B.Gen.Jack.O.Neill
B.Gen.Jack.O.Neill

Reputation: 8397

Database inheritance linking

I have basic generalisation schema in database, I have superclass vehicle, and its subclases automobiles, motorcycles. I implemented those by creating table for each superclass/subclas combination, thus I created automobiles and motorcycles tables.

But now I have problem, I need to set FK from my other table to "both" these tables. So "Person" has either motorcycle or automobile, but never both and never more from one kind. How can I link table Persons with Automobiles and Motorcycles to achieve that? Thank you very much.

Upvotes: 1

Views: 36

Answers (2)

Thomas Weller
Thomas Weller

Reputation: 11717

You could possibly do this with a check constraint or a stored procedure (the concrete implementation details depend on your DBMS).

But I think you should rethink your overall architecture. Inheritance is an OO concept and clearly should be implemented by the client code, not by the database. Databases (at least the relational ones) are not intended to handle such things, and they're not good at it. You're making your life unnecessarily hard (and you open the door for many hard-to-find errors), when you're doing it anyway...

HTH!

Upvotes: 0

Deleted
Deleted

Reputation: 4998

Don't do it like that - it will just result in pain and lots of expensive joins.

Have one table for the entire class heirarchy and have a "discriminator" column which determines the type either numerically or symbolically. Quick example using psuedo-sql:

create table machine {
    machine_id int auto_increment,
    machine_type char(1),  -- can be 'a' for auto or 'm' for motorcyle,
    make varchar(255),
    model varchar(255),
    auto_specific_field varchar(10) null,
    moto_specific_field varchar(10) null
}

Upvotes: 1

Related Questions