Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

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
share|improve this answer
    
I went with 1b and 2b because it's consistent and because I realized I wanted an identity column for each table so I could easily count the records. If the derived table mirrored the base table, the PK of the derived tables would look goofy. That identity column doesn't necessarily have to be the PK. If there is a composite PK (with more than one column making it unique) and also being a derived table, I kept the identity column as an ordinary column and not as part of the PK. –  MacGyver Dec 21 '13 at 5:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.