关系数据库设计多种用户类型 [英] Relational database design multiple user types
问题描述
我有4种类型的用户，每个都有特定的数据，但它们也共享公共数据，例如 username ， password ..
我的第一个想法是创建一个主要的用户表与 user_type 列。然后当查询用户数据时，我可以先选择他们的 user_type ，然后根据输出运行不同的查询来抓取用户类型具体数据。
我不喜欢这个，因为我希望我可以用一个查询获取所有用户相关数据，最好是使用外键。
第二个想法是没有在用户表中使用 user_type 列，而是使用特定用户类型表中的外键指向一行主要的用户表。
我喜欢这一点，虽然我想我将不得不运行N个查询，其中N是用户类型的数量，每次我需要抓住用户数据。
有没有其他的选择？在这种情况下，好的做法是什么？
非常感谢
您的案例看起来像一个类/子类的实例。
有两种经典的方法来设计SQL表来处理子类。每个都有优点和缺点。
一种方法称为单表继承。在这个设计中，所有类型用户只有一张表。如果给定的列不属于给定行，则交集将保留为NULL。可以添加一列以指示用户类型。
另一种方法叫做Class Table Inheritance。这很像Nanego给出的答案，还有一些微小的变化。有一个表用于用户，所有公共数据和一个id字段。每个子类有一个表，数据与该子类有关。 id字段通常设置为users表中的匹配行中的id字段的副本。这样，子类密钥可以执行双重任务，充当主键和引用用户表的外键。最后一种技术称为共享主键。它在插入时需要一点编程，但它是非常值得的。它实现了关系的一对一性质，并加速了必要的联合。
您可以将这三个设计作为SO或
单表继承
class-table-inheritance
共享主键
I have a 4 types of users and each have specific data, but they also share commun data, like username, password ..
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.
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 ?
Many thanks
Your case looks like an instance of class/subclass.
There are two classic ways to design SQL tables to deal with subclasses. Each has advantages and disadvantages.
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.
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.
You can look up all three of these designs as tags in SO or as articles out on the web.
single-table-inheritance class-table-inheritance shared-primary-key
这篇关于关系数据库设计多种用户类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
