Three common strategies: Create a table for each class in the hierarchy that contain the properties defined for each class and a foreign key back to the top-level superclass table. So you might have a vehicle table with other tables like car and airplane that have a vehicle_id column. The disadvantage here is that you … Read more
I did as Donnie suggested, but without the role field, because it complicates things. This is the final implementation: DDL: CREATE TABLE Employee ( ast VARCHAR(20) not null, firstname VARCHAR(200) not null, surname VARCHAR(200) not null, … PRIMARY KEY(ast) ); CREATE TABLE Administrative ( employee_ast VARCHAR(20) not null REFERENCES Employee(ast), PRIMARY KEY(employee_ast) ); CREATE TABLE … Read more
Martin Fowler discusses this extensively in his book Patterns of Enterprise Application Architecture book. Get this book and look into: Single Table Inheritance Class Table Inheritance Concrete Table Inheritance The Website should give you some idea. You might also want to read the section on inheritance mappers. Each of the different approaches have their pros … Read more
The problem here is that you mix “table-per-class” inheritance and GenerationType.Auto. Consider an identity column in MsSQL. It is column based. In a “table-per-class” strategy you use one table per class and each one has an ID. Try: @GeneratedValue(strategy = GenerationType.TABLE)
@Bill Karwin describes three inheritance models in his SQL Antipatterns book, when proposing solutions to the SQL Entity-Attribute-Value antipattern. This is a brief overview: Single Table Inheritance (aka Table Per Hierarchy Inheritance): Using a single table as in your first option is probably the simplest design. As you mentioned, many attributes that are subtype-specific will … Read more