0

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.

1 Answer 1

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.

4
  • Oh thank you, that sounds like a smart solution. I will try that!
    – Sven M.
    Oct 13, 2020 at 6:31
  • Btw what just went through my mind. Would you design the attribute "Type" as int2 or would you capture an enum for better readability?
    – Sven M.
    Oct 13, 2020 at 6:32
  • 1
    Use enums only if you are very certain that you will never have to remove an enum value, because that's impossible. Other than that, it doesn't matter. Oct 13, 2020 at 6:38
  • This is a good and important point, I had completely forgotten.
    – Sven M.
    Oct 13, 2020 at 6:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.