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:
https://stackoverflow.com/questions/190296/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.'
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`
How to model inheritance of two tables mysql
https://stackoverflow.com/questions/3074535/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.
Best Answer
Whether you are better off with single-table-inheritance or class-table-inheritance really depends on the particulars of your case. The performance advantage can go either way. The difficulties presented by having lots of NULLS in a table range from the trivial to the overwhelming. It depends on what your data looks like, and what you intend to do.
Kudos for figuring out that the problem is basically due to a mismatch between object modeling and relational modeling.
PS if you use UserID as the primary key on both Parent and Babysitter, and also declare it as a foreign key, you'll get some benefits, at the cost of a little programming when you go to insert new users. This technique is called shared-primary-key, and it's also presented over on SO.