- When class inheritance is used, the child table inherits the key from the parent, right? Thus, you
INSERT INTOthe parent and then the child?
- This indicates that the PK of the child table (derived class) becomes FK in the parent table (base class). See "but now the primary key of these tables also becomes a foreign key to the People table." That's not true is it? The examples indicate that the child tables reference the parent table. Did I misunderstand something?
- Can the PK of both the child and parent tables be the same?
I'm new to handling supertype-subtype instances in RDBs, but I've read a number of posts about three main options. The one I'm interested to implement is class inheritance where I have a base table with all common attributes and derived tables with attributes that are unique to each subtype.
I generically understand that class inheritance and table inheritance aren't the same. Based on the postgres documentation, table inheritance will allow for duplicate data in the child tables that I can't allow. Thus, I don't think table inheritance is a good way for me to go.
I'm interested in class inheritance because I need multiple different subtypes to be referenced as FK in a table. For example, I have a base class table
vehicle and derived classes
car. I have another table for
vehicle_maintenance. As a side note, I have many tables that are equivalent to
vehicle_maintenance in that they can be applied to both
car. I also have many more subtypes than just
car with many attributes that are unique to each subtype.
Here's an example adapted from this post.
CREATE TABLE vehicle ( vehicle_id int PRIMARY KEY, paint_type text, -- // other common attributes ); CREATE TABLE boat ( boat_id int PRIMARY KEY REFERENCES vehicle (vehicle_id), propeller_type text, water_exposure text, -- // other attributes specific to boat ... ); CREATE TABLE car ( car_id int PRIMARY KEY REFERENCES vehicle (vehicle_id), steering_type text, wheel_type text, -- // other attributes specific to car ... ); CREATE TABLE vehicle_maintenance_id ( vehicle_maintenance_id int, date_complete datetime, FOREIGN KEY (vehicle_id) REFERENCES vehicle (vehicle_id) );
In this example, I would create a unique ID for every vehicle in the base table and carry that over to the derived tables. This would mean that the same
paint_type would be repeated without any other attributes being
UNIQUE, except the PK. This doesn't seem very normalized, but it's the only way I understand to get all of the PKs from the derived tables into one table without just doing an all-in-one table instead of class inheritance.
Am I just confusing myself, and the example I provided above is acceptable?
Perhaps I need to make this a separate question: I can't create composite keys based on attributes in the child tables and use them as PKs in the base table, right? This is because each child table will have unique attributes and the base table would require only one set of attributes to form the composite FK, correct?