关系型数据库设计多用户类型 [英] Relational database design multiple user types

问题描述

我有 4 种类型的用户，每一种都有特定的数据，但他们也共享公共数据，例如 username、password ..

I have a 4 types of users and each have specific data, but they also share commun data, like username, password ..

我的第一个想法是创建一个带有 user_type 列的主 users 表.然后在查询用户数据时，我可以先选择他们的 user_type，然后根据 output 运行不同的查询来获取用户类型"特定数据.我不喜欢这个，因为我希望我可以通过一个查询获取所有与用户相关的数据，最好使用外键.

My first thought is to create a main users table with user_type column. Then when querying user data i can just first select their user_type and then depending on the output run a different query to grab "user type" specific data. I am not fond of this as i wish i could grab all user related data with one query and ideally using Foreign Keys.

第二个想法是在 users 表中没有 user_type 列，而是使用来自特定用户类型表的外键将指向主 <代码>用户表.我更喜欢这样，但我想我将不得不运行 N 个查询，其中 N 是每次我需要获取用户数据时的用户类型数.

Second idea is to not have a user_type column in the users table and instead use foreign key that from a specific user type table will point to a row the main users table. I like that a bit better though i guess i will have to run N queries, where N is the number of user type every time i need to grab user data.

还有其他选择吗?在这种情况下，什么是好的做法?

Are there any other options ? What would be the good practice in such a case ?

非常感谢

推荐答案

您的案例看起来像是类/子类的实例.

Your case looks like an instance of class/subclass.

有两种经典的方法来设计SQL表来处理子类.每个都有优点和缺点.

There are two classic ways to design SQL tables to deal with subclasses. Each has advantages and disadvantages.

一种方式称为单表继承".在这个设计中，所有类型的用户只有一张桌子.如果给定的列不属于给定的行，则交集为 NULL.可以添加一列来指示用户类型.

One way is called "Single Table Inheritance". In this design there is just one table for all types of users. If a given column doesn't pertain to a given row, the intersection is left NULL. A column can be added to indicate the user type.

另一种方式称为类表继承".这很像 Nanego 给出的答案，只是做了一些小改动.有一个供用户使用的表，包含所有公共数据和一个 id 字段.每个子类都有一个表，其中包含与该子类有关的数据.id 字段通常设置为用户表中匹配行中 id 字段的副本.这样子类键可以做双重任务，既充当主键又充当引用用户表的外键.最后一种技术称为共享主键".它需要在插入时进行一些编程，但非常值得.它加强了关系的一对一性质，并加快了必要的连接.

Another way is called "Class Table Inheritance". This is much like the answer Nanego gave, with a few minor changes. There is one table for users, with all the common data, and a id field. There is one table for each subclass, with data that pertains to that subclass. The id field is often set up as a copy of the id field in the matching row back in the users table. This way the subclass key can do double duty, acting as both a primary key and as a foreign key referencing the user table. This last technique is called "Shared Primary Key". It requires a little programming at insert time, but it's well worth it. It enforces the one-to one nature of the relationship, and it speeds up the necessary joins.

您可以将所有这三种设计作为 SO 中的标签或网络上的文章进行查找.

You can look up all three of these designs as tags in SO or as articles out on the web.

single-table-继承class-table-inheritanceshared-primary-key

这篇关于关系型数据库设计多用户类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！