5

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.

3

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.

  • Thank you for your response! In the case that a 'Parent' also has a list of 'Children' (so another table children) do you favor class-table-inheritance over single-table-inheritance for clarity/readability? Could you explain the performance impact a bit more in detail? – Jdruwe Feb 24 '16 at 14:46
  • In every parent child relationship I've ever dealt with, the advantages were on the side of single table inheritance. Basically, the attributes were almost all common attributes, and NULLS were not a concern. You still have to do a join to form an "all my children" query. The FK in this case is a dependent attribute that references a different row in the same table. and people who are nobody's child (in the database) have a NULL inthe FK. YMMV. – Walter Mitty Feb 24 '16 at 15:39
  • The performance considerations are these: joins are costly, although less costly than most newbies think. The right indexes, and a well optimized DBMS make all the difference. Fat tables are costly, because it costs more to read each row, even when most of the data won't be used. There's a trade-off between skinny tables and few joins. All of this is DBMS specific, but a lot of products follow this general pattern. – Walter Mitty Feb 24 '16 at 15:42
  • So say that if my entities only differ in at most 1 attribute/column I should use single table inheritance because the ocasional NULL has no imact? I am not sure if you understood my chikdren question. I was thinking about a seperate table "children" that contains for example child A with a FK to a Row in Table users (in case of single table inheritance), that row is ofcourse a partent and not a babysitter. In my opinion this is not a problem, i am just asking this because there will be a mix of both babysitter and parent users. Thanks in advance :) – Jdruwe Feb 24 '16 at 16:01
  • you are right: I misunderstood your children question. I also didn't undertand whether you were talking about parent-child as people or whether you were speaking generically about one-to-many relationships, or so called "has-a" relationships. Whether the parents and the children belong in the same table or different tables depends on whether we model them as the same type or different types (or maybe subtypes as in the prior discussion). – Walter Mitty Feb 24 '16 at 17:31
0

Thanks to @Walter Mitty I am now using the shared-primary-key technique which results in the following design (http://www.tutorialspoint.com/jpa/jpa_advanced_mappings.htm, JPA Joined inheritance):

enter image description here

Don't mind the weird FK name, it is autogenerated.

  • 1
    Weird constraint names were also a feature of the RDBMS I used, back in the day. – Walter Mitty Feb 24 '16 at 19:49
  • Looks good to me. – Walter Mitty Feb 24 '16 at 19:50

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.