Suppose we have three concepts as bellow:
Concept ( attribute1, attribute2, .. )
A ( a, b, c, d )
B ( a, b, c, d, e )
C ( a, b, c, d, f )
There are three options to model these three concepts in the database:
1) To model them as they are
Table { column1, column2, ... }
A { id, a, b, c, d }
B { id, a, b, c, d, e }
C { id, a, b, c, d, f }
Con: There is data redundancy.
2) Model all in one table
A { id, object_type, a, b, c, d, e, f }
Con: Some fields remain empty for some concepts.
3) Use relation between tables
A { parent_id, a, b, c, d }
B { id, parent_id, e }
C { id, parent_id, f }
Con: Increase query complexity to join tables.
Which method would you use? Do you have other solutions? Do you think the third method has less performance?
Clearly, your option 1 is the worst choice. The main problem here is that you need to duplicate all rows of B and C in A, thus creating a severe maintenance problem.
Your option 2 is called the Single Table Inheritance pattern, which is recommended in cases where subtables do not have (m)any additional columns.
Your option 3 is called the Joined Tables Inheritance pattern where subtables (representing subclasses) are joined to their supertable via their primary key being also a foreign key referencing the supertable.
So, for the case of your abstract example, it seems that option 2 is the recommended approach, since your tables B and C have only one additional column.
Notice that according to the Joined Tables Inheritance approach, there is no need to add a primary key attribute (as you did with id in B and C). You just use the same primnary key as the supertable and make it also a foreign key referencing the supertable. So, in this approach, the schema would be
A { id PK,
a, b, c, d
}
B { id PK REFERENCES A,
e
}
C { id PK REFERENCES A,
f
}
You can read more about this in the section Subtyping and Inheritance with Database Tables of my tutorial about developing front-end web applications with class hierarchies.