CODEWITHSUNDEEP
postgresqldatabase-design
Albert
Albert

Reputation: 2664

Database design for multiple types of entities to use with TypeOrm

I have a few types of users: FIGHTER, JUDGE, SPECTATOR, PROMOTER, LOCATION_PROVIDER. They all have some parameters, some are common, others are specific. I reason, that it would be logical to have a separate tablea for each type of user. Each such table would store only relevant to a specific type of user data.

As far as I can see from the documentation available here:

https://github.com/typeorm/typeorm/blob/master/docs/entity-inheritance.md

TypeOrm suggests a number of solutions implying inheritance but they are all about reducing the amount of code. For instance, the pattern called Single Table Inheritance allows to do some separation of entities code-wise, but yet all types of users would be dumped into a single table, which is sort of obvious from the name of the pattern. Other solutions are no better for me.

I was thinking of creating a table users to store the common data and then creating all other tables that would store type specific data.

For example,

user has such fields as first_name, second_name, age.

fighter has such fields as weight, wins, losses, draws.

But the problem is that these tables happen to be unrelated. So does it mean that I have to make up some logic that would make them related? Not sure I understand how best to approach it.

Upvotes: 2

Views: 2645

Answers (1)

IMujagic
IMujagic

Reputation: 1259

The best answer to such questions is: It depends. Seriously, it depends on how you will access your data and which part of the stack is more important to you. If you will process your users regardless of their type and query them all together, then it makes sense to keep them together. However, if each type is going to be accessed and processed separately then it's better to keep every concrete type in it's own table.

I usually approach such problems by considering following scenarios:

  • DB Design is most important and the application is operating always on one user type at a time

    If you want to focus on DB design and make a let's say normalized schema and your application is more likely to operate only on one user type at a time, in this case, I would add one user table that is a "root" which has an user_id and some other common personal information columns. For each other concrete type of user I would add a separate table with a foreign key to user table user_id column.

  • I can live with the not perfect design from a DB point of view and application is going to operate on different user types at the same time

    In this case add one user table with all possible type specific columns which will be nullable of course. Besides the required common columns like first_name, 'last_name' etc... you need a column that will tell you the type of user. You can create either a separate table user_type (1 - Fighter, 2 - Judge etc...) and reference it from user table or you keep it simple and just save a string "FIGHTER", "JUDGE" directly inside user table and handle it on the application side with some enum type.

I usually prefer the first option because it's more logical and much more cleaner, but as said, it depends on your end use case.

Upvotes: 3

Related Questions