Modeling Table-Per-Type in PostgreSQL
I think that you data model makes sense. I would add type to the &quot;subclass&quot; tables, create a unique constraint on (system_user_id, type) and create foreign key relationships pointing to system_user. Then add a check constraint to the subclass tables that makes sure that type has the correct value.
This avoids using a trigger.