When using class inheritance, can all PKs of the derived tables exist as PKs of the base table?

Question

Brief questions

1. When class inheritance is used, the child table inherits the key from the parent, right? Thus, you INSERT INTO the parent and then the child?
   1. 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?
2. Can the PK of both the child and parent tables be the same?

Details

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 boat and 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 boat and car. I also have many more subtypes than just boat and 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?

Edit

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?

Answer 1

Your data model looks reasonable and normalized. If you are using the same paint_type repeatedly, you can break it out into a lookup table.

I don't understand the concern in your edit, so maybe you should explain that with more detail in a different question.