Proper implementation of child (derived) table PK/FK during single and multiple inheritance hard coded generalization physical data model

database-designforeign keygeneralizationprimary-key

Below is an image of hard coded generalization UML logical model of entities. I'd like to implement these classes as tables in a physical data model.

Two derived classes/entities (Class2 and Class5) show single inheritance generalization because they each are derived from a single base class/entity. Class2 IS-A Class1. Class5 IS-A Class4.

And Class3 shows multiple inheritance generalization because it has two base tables (or two parent entities). Class3 IS-A (Class1 AND Class4). Or you could read it Class3 IS-A Class1 OR Class3 IS-A Class4.

Here are my questions. Choosing one example from each. I'd like to be consistent. How should this be implemented? Or maybe I missed a scenario?

1.) Single inheritance: How would the PK/FK values get implemented in the derived tables (Class2 for example)?

(1a) Have attribute1 be PK of derived table and be FK #1 (pointing to only parent/base table PK).

OR…

(1b) Have attribute1 be PK of derived table. And attribute2 be the FK to derived table (pointing to only base/parent table PK).

2.) Multiple inheritance: How would the PK/FK values get implemented in the derived table (Class3 for example)?

(2a) Have attribute1 be PK of derived table and be FK #1 (pointing to first parent/base table PK). Then have attribute2 be FK #2 (pointing to second parent/base table PK).

OR…

(2b) Have attribute1 be PK of derived table. And have attribute2 be FK #1 (pointing to first parent/base table PK). And have attribute3 be FK #2 (pointing to second parent/base table PK).

enter image description here

Best Answer

I solved this (for single inheritance) like so:

each extension of a class inherits, as its primary key the base classes primary key and always targets with its fk the immediate base class:

class A
class B extends A
class C extends B

A(id,p1)
B(id,p1,p2)
B(id,p1,p2,p3)

Table A:
id: primary key
p1: sth

Table B:
id: primary key + foreign key -> A.id
p2: sth

Table C:
id: primary key + foreign key -> B.id
p3: sth

this would reqiure a join to completely retrieve B or C.

alternatively you can have each extension mirror the properties of the base classes:

Table A:
id: primary key
p1: sth

Table B:
id: primary key + foreign key -> A.id
p1: sth
p2: sth

Table C:
id: primary key + foreign key -> B.id
p1: sth
p2: sth
p3: sth

here you can retrieve any class without joins but data is duplicated and has to be kept consistent.

which solution you can use strongly depends on how you want to retrieve the data and if you can live with the overhead of the join in version 1 or overhead of writing data multiple times in version 2.

I also added a meta-column to the table to know if a row does have extensions or not which is useful for a some queries:

Table A:
id: primary key
p1: sth
class: A,B,C

Table B:
id: primary key
p2: sth
class: B,C

Table C:
id: primary key
p2: sth
class: C

Related Solutions

Referencing multiple tables from a single table and store additional data

I think what you're missing is a car_configuration table. This will have an FK to the cars table and have car_config_parts and car_config_tunings as children, like so:

create table car_configuration (
  config_id integer primary key,
  car_id integer references cars (car_id)
);

create table car_config_parts (
  config_id integer references car_configuration (config_id),
  part_id integer references parts (part_id),
  constraint car_config_part_pk primary key (config_id ,part_id)
);

create table car_config_tunings (
  config_id integer references car_configuration (config_id),
  tuning_id integer references tuning (tuning_id),
  constraint car_config_tuning_pk primary key (config_id, tuning_id)
);

You can then include the config_id as a reference in the timeset table (with no reference to cars). You'll need to ensure that you can't change the parts and tunings for a configuration or implement some form of versioning on this table to ensure you can reconstruct the exact details used to set a given time in the past.

Sql-server – Implementing foreign keys on generalized data model

You can have two (or more) foreign keys in a table. They can be both regarding the same attribute and referencing different tables. So you could have 2 foreign keys from racoon(pk), one referencing wild(pk) and the other referencing domestic(pk). Something like:

CREATE TABLE raccon
( mammal_id INT  PRIMARY KEY
, FOREIGN KEY (mammal_id)
    REFERENCES domestic (mammal_id)
, FOREIGN KEY (mammal_id)
    REFERENCES wild (mammal_id)
) ;

(assuming all tables have mammal_id as the primary key)

Best Answer

Related Solutions

Referencing multiple tables from a single table and store additional data

Sql-server – Implementing foreign keys on generalized data model

Related Question