Postgresql inheritance based database design

Question

I'm developing a simple babysitter application that has 2 types of users: a 'Parent' and the 'Babysitter'. I'm using postgresql as my database but I'm having trouble working out my database design.

The 'Parent' and the 'Babysitter' entities have attributes that can be generalized, for example: username, password, email, ... Those attributes could be placed into a parent entity called 'User'. They both also have their own attributes, for example: Babysitter -> age.

In terms of OOP things are very clear for me, just extend the user class and you are good to go but in DB design things are differently. Before posting this question I roamed around the internet for a good week looking for insight into this 'issue'. I did find a lot of information but
it seemed to me that there was a lot a disagreement. Here are some of the posts I've read:

How do you effectively model inheritance in a database?: Table-Per-Type (TPT), Table-Per-Hierarchy (TPH) and Table-Per-Concrete (TPC) VS 'Forcing the RDb into a class-based requirements is simply incorrect.'

https://dba.stackexchange.com/questions/75792/multiple-user-types-db-design-advice:

Table: `users`; contains all similar fields as well as a `user_type_id` column (a foreign key on `id` in `user_types`
Table: `user_types`; contains an `id` and a `type` (Student, Instructor, etc.)
Table: `students`; contains fields only related to students as well as a `user_id` column (a foreign key of `id` on `users`)
Table: `instructors`; contains fields only related to instructors as well as a `user_id` column (a foreign key of `id` on `users`)
etc. for all `user_types`

https://dba.stackexchange.com/questions/36573/how-to-model-inheritance-of-two-tables-mysql/36577#36577

When to use inherited tables in PostgreSQL?: Inheritance in postgresql does not work as expected for me and a bunch of other users as the original poster points out.

I am really confused about which approach I should take. Class-table-inheritance (https://stackoverflow.com/tags/class-table-inheritance/info) seems like the most correct in my OOP mindset but I would very much appreciate and updated DB minded opinion.

Answer 1

The way that I think of inheritance in the database world is "can only be one kind of." No other relational modeling technique works for that specific case; even with check constraints, with a strict relational model, you have the problem of putting the wrong "kind of" person into the wrong table. So, in your example, a user can be a parent or a babysitter, but not both. If a user can be more than one kind-of user, then inheritance is not the best tool to use.

The instructor/student relationship really only works well in the case where students cannot be instructors or vice-versa. If you have a TA, for example, it's better to model that using a strict relational design.

So, back to the parent-babysitter, your table design might look like this:

CREATE TABLE user (
  id SERIAL,
  full_name TEXT,
  email TEXT,
  phone_number TEXT
);

CREATE TABLE parent (
  preferred_payment_method TEXT,
  alternate_contact_info TEXT,
  PRIMARY KEY(id)
) INHERITS(user);

CREATE TABLE babysitter (
  age INT,
  min_child_age INT,
  preferred_payment_method TEXT,
  PRIMARY KEY(id)
) INHERITS(user);

CREATE TABLE parent_babysitter (
  parent_id INT REFERENCES parent(id),
  babysitter_id INT REFERENCES babysitter(id),
  PRIMARY KEY(parent_id, babysitter_id)
);

This model allows users to be "only one kind of" user - a parent or a babysitter. Notice how the primary key definitions are left to the child tables. In this model, you can have duplicated ID's between parent and babysitter, though this may not be a problem depending on how you write your code. (Note: Postgres is the only ORDBMS I know of with this restriction - Informix and Oracle, for example, have inherited keys on inherited tables)

Also see how we mixed the relational model in - we have a many-to-many relationship between parents and babysitters. That way we keep the entities separated, but we can still model a relationship without weird self-referencing keys.

Answer 2

All the options can be roughly represented by following cases:

1. base table + table for each class (class-table inheritance, Table-Per-Type, suggestions from the dba.stackexchange)
2. single table inheritance (Table-Per-Hierarchy) - just put everything into the single table
3. create independent tables for each class (Table-Per-Concrete)

I usually prefer option (1), because (2) and (3) are not completely correct in terms of DB design.

With (2) you will have unused columns for some rows (like "age" will be empty for Parent). And with (3) you may have duplicated data.

But you also need to think in terms of data access. With option (1) you will have the data spread over few tables, so to get Parent, you will need to use join operations to select data from both User and Parent tables.

I think that's the reason why options (2) and (3) exist - they are easier to use in terms of SQL queries (no joins are needed, you just select the data you need from one table).