关系型数据库设计多用户类型

Question

我有4种类型的用户，每个用户都有特定的数据，但他们也共享公共数据，比如用户名，密码等。

我的第一个想法是创建一个带有user_type列的主users表。然后在查询用户数据时，我只需先选择其user_type，然后根据输出运行不同的查询以获取“用户类型”特定数据。 我不喜欢这个想法，因为我希望能够使用外键一次性获取所有与用户相关的数据，并且最好只用一个查询。

第二个想法是在users表中不使用user_type列，而是使用外键，从特定用户类型的表中将指向主users表行的指针。 我更喜欢这个想法，尽管我想每次需要获取用户数据时都要运行N个查询，其中N是用户类型的数量。

还有其他选项吗？在这种情况下，什么是良好的实践？

非常感谢

- silkAdmin

---

如果我理解你的意思，你只需要在你的SQL语句中使用JOIN子句。类似这样SELECT * FROM users U LEFT JOIN user_details D ON U.id = D.user_id WHERE U.id = ?。 - Benny Hill

一个用户可以同时拥有多种类型吗？ - Mike Sherrill 'Cat Recall'

@Catcall 在这种情况下，用户只能拥有一种类型，但我很好奇在任何情况下都可能出现什么情况。 - silkAdmin

Answer 1

您的情况看起来像是类/子类的实例。

有两种经典的方法可以设计SQL表以处理子类。每种方法都有优点和缺点。

一种方法称为“单表继承”。在这种设计中，所有类型的用户只有一个表。如果给定的列与给定的行不相关，则交叉处留空。可以添加一个列来指示用户类型。

另一种方法称为“类表继承”。这与Nanego给出的答案很相似，但有一些小改变。有一个用户表，包含所有公共数据和一个id字段。对于每个子类别，都有一个表，其中包含属于该子类别的数据。id字段通常设置为与用户表中匹配行的id字段的副本。这样，子类关键字可以做双重任务，既充当主键，又充当引用用户表的外键。这种最后一种技术称为“共享主键”。它需要插入时进行一些编程，但非常值得。它强制执行关系的一对一性，并加速必要的连接。

您可以在SO上查找这三个设计标记或在网络上查找文章。

single-table-inheritance class-table-inheritance shared-primary-key

- Walter Mitty

---

谢谢你提供这些术语，我已经搜索了它们并学到了很多。 - silkAdmin

Answer 2

尽管将用户拆分到不同的表中可以更有效地利用磁盘空间，但问题在于您实际上需要有条件地进行连接操作 - 根据用户类型加入到用户类型特定的表。这在SQL中编写非常麻烦，而现在谁还关心磁盘空间呢？

更好的选择是使用一个用户表，该表具有足够的列来存储有关任何用户类型的信息，知道某些列不会用于某些用户类型。未使用列的“低效性”将在执行速度和查询简单性方面得到更多的补偿。

如果出现另一种用户类型，则也很容易扩展 - 添加列比添加表要容易得多，并且随着您添加更多的用户类型，对新列的需求将减少（您只需要存储有关用户的少量不同信息）。

- Bohemian

---

我不太想点赞这个，因为它感觉有点马虎，但它确实是真的。 - Eric Petroelje

我同意避免使用连接语句可以使代码更清晰，但是我希望能够拥有与PHP类匹配的表格，这样会感觉更优雅。 - silkAdmin

我也看到这种方法的一个缺点是表失去了完整性，因为我必须使所有字段可为空，这可能会导致后端代码出错。 - silkAdmin

Answer 3

我的做法是创建一个名为“Person”的表，其中包含共同的字段，以及每种类型用户的一个表，该表具有外键“person_id”。

在您的请求中，您只需使用外键连接两个表即可获取一个类型用户的所有数据。

您有多少种类型的用户？

- Nanego

---

现在是4，但我想保持灵活性。我提到的第二个解决方案实际上就是你建议的。 - silkAdmin