时间戳

首页 > 解决方案 > Database design for multiple types of entities to use with TypeOrm

postgresql - 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.

标签： postgresqldatabase-design

解决方案

此类问题的最佳答案是：视情况而定。说真的，这取决于您将如何访问数据以及堆栈的哪一部分对您更重要。如果您将处理您的用户而不考虑他们的类型并将它们全部查询，那么将它们保持在一起是有意义的。但是，如果每种类型都将被单独访问和处理，那么最好将每种具体类型保存在它自己的表中。

我通常通过考虑以下场景来解决此类问题：

  • 数据库设计是最重要的，应用程序一次总是在一种用户类型上运行

    如果您想专注于 DB 设计并制作一个标准化模式，并且您的应用程序更有可能一次仅对一种用户类型进行操作，在这种情况下，我将添加一个user“根”表，该表具有user_id以及其他一些常见的个人信息栏目。user对于每个其他具体类型的用户，我会在表列中添加一个带有外键的单独表user_id。

  • 从数据库的角度来看，我可以忍受不完美的设计，并且应用程序将同时在不同的用户类型上运行

    在这种情况下，添加一个user表，其中包含所有可能的类型特定列，这些列当然可以为空。除了像 'last_name' 等所需的常用列之外first_name，您还需要一个能够告诉您用户类型的列。您可以创建一个单独的表user_type（1 - Fighter，2 - Judge 等...）并从user表中引用它，或者您保持简单，只需将字符串“FIGHTER”，“JUDGE”直接保存在user表中并在表中处理它具有某种enum类型的应用程序端。

我通常更喜欢第一个选项，因为它更合乎逻辑且更简洁，但如上所述，这取决于您的最终用例。

推荐阅读