Modeling Table-Per-Type in PostgreSQL

Question

I have read the following post How do you effectively model inheritance in a database? about inheritance in databases. Unfortunately I could not find anything in the PostgreSQL books from Packt. And now I'm a bit confused if my approach is an overkill and doesn't even belong in the database or makes sense (see my considerations at the end).

First I wanted to implement the following example using Table-Per-Concrete. But then I read several times that you should only do this when you really have performance problems and you should use table per type as long as you have no performance problems.

So the following relations are given:

system_user (system_user_id, type, first_name, last_name, ...)

user (system_user_id) *Has no unique attributes but special relations accessible only for him, e.g. orders
customer (system_user_id, company_name, ...)
supporter (system_user_id, nickname, ...)

So far so good, but I was wondering how to ensure in the above example that:

• The type cannot be changed.
• A system_user has at most one entry in an extension.

My consideration:

• A trigger that throws an error if the type is changed during an UPDATE.
• One trigger for each extension, which checks before an INSERT, if an entry with this system_user_id already exists in another extension.

Does this make sense? Or am I going about it the wrong way? Is this even necessary? Should I create a separate relation for each entity directly?

Maybe I have too much of an object-oriented software developer view on it. Thanks a lot for your answers.

Answer 1

I think that you data model makes sense. I would add type to the "subclass" 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.