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.'
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://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.