I have a 4 types of users and each have specific data, but they also share commun data, like username, password ..
我有4种类型的用户，每种都有特定的数据，但他们也共享公共数据，如用户名，密码..
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.
我的第一个想法是使用user_type列创建一个主用户表。然后，当查询用户数据时，我可以先选择他们的user_type，然后根据输出运行不同的查询来获取“用户类型”特定数据。我不喜欢这个，因为我希望我能用一个查询获取所有用户相关数据，理想情况下使用外键。
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.
第二个想法是在users表中没有user_type列，而是使用来自特定用户类型表的外键将指向主用户表的行。我喜欢那样好一点虽然我想我将不得不运行N个查询，其中N是每次我需要获取用户数据时的用户类型数。
Are there any other options ? What would be the good practice in such a case ?
还有其他选择吗？这种情况下的良好做法是什么？
Many thanks
非常感谢
3 个解决方案
#1
14
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.
有两种经典的方法来设计SQL表来处理子类。每个都有优点和缺点。
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.
一种方法称为“单表继承”。在此设计中，只有一个表适用于所有类型的用户。如果给定列不属于给定行，则交集将保留为NULL。可以添加列以指示用户类型。
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.
另一种方法称为“类表继承”。这很像Nanego给出的答案，只有一些小的改动。用户可以使用一个表，包含所有常用数据和一个id字段。每个子类都有一个表，其中包含与该子类相关的数据。 id字段通常设置为users表中匹配行中的id字段的副本。这样，子类键可以执行双重任务，既充当主键又充当引用用户表的外键。最后一种技术称为“共享主键”。它需要在插入时进行一些编程，但它非常值得。它强制执行关系的一对一性质，并加速必要的连接。
You can look up all three of these designs as tags in SO or as articles out on the web.
您可以在SO中查找所有这三个设计作为标签，也可以在网络上查找文章。
single-table-inheritance class-table-inheritance shared-primary-key
单表继承class-table-inheritance shared-primary-key
#2
2
Although it's more efficient use of disk space, the problem with splitting into separate tables is that you effectively require conditional joins - joining to the user-type specific table based on the user_type. This is a pain to code as SQL, and these days who cares about disk space?
尽管使用磁盘空间效率更高，但拆分成单独表的问题在于您实际需要条件连接 - 根据user_type连接到特定于用户类型的表。代码作为SQL很痛苦，而现在关心磁盘空间的人呢？
The better option is to have one user table with enough columns to store information about any user type, knowing that some columns won't be used for some user types. The "inefficiency" of having unused columns will more than be compensated for in execution speed and query simplicity.
更好的选择是让一个用户表具有足够的列来存储有关任何用户类型的信息，因为知道某些用户类型不会使用某些列。使用未使用的列的“低效率”将在执行速度和查询简单性方面得到补偿。
It's also easily extendible, in case you get another user type - it's far easier to add columns than it is to add tables, and as you added more user types, the requirement for new columns would diminish (there just aren't that many different things about a user you need to store)
它也很容易扩展，如果你得到另一个用户类型 - 添加列比添加表要容易得多，并且随着你添加更多用户类型，新列的需求会减少（没有那么多不同关于你需要存储的用户的事情）
#3
0
My way to do it would have been to create one table "Person" with the common fields, and one table for each type of user with a foreign key "person_id".
我这样做的方法是创建一个带有公共字段的表“Person”，以及一个带有外键“person_id”的每种用户的表。
In your request, you just have to join two tables with the foreign_key in order to get all data for one type of user.
在您的请求中，您只需要使用foreign_key连接两个表，以获取一种类型用户的所有数据。
How many types of user do you have ?
您有多少类型的用户？
#1
14
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.
有两种经典的方法来设计SQL表来处理子类。每个都有优点和缺点。
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.
一种方法称为“单表继承”。在此设计中，只有一个表适用于所有类型的用户。如果给定列不属于给定行，则交集将保留为NULL。可以添加列以指示用户类型。
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.
另一种方法称为“类表继承”。这很像Nanego给出的答案，只有一些小的改动。用户可以使用一个表，包含所有常用数据和一个id字段。每个子类都有一个表，其中包含与该子类相关的数据。 id字段通常设置为users表中匹配行中的id字段的副本。这样，子类键可以执行双重任务，既充当主键又充当引用用户表的外键。最后一种技术称为“共享主键”。它需要在插入时进行一些编程，但它非常值得。它强制执行关系的一对一性质，并加速必要的连接。
You can look up all three of these designs as tags in SO or as articles out on the web.
您可以在SO中查找所有这三个设计作为标签，也可以在网络上查找文章。
single-table-inheritance class-table-inheritance shared-primary-key
单表继承class-table-inheritance shared-primary-key
#2
2
Although it's more efficient use of disk space, the problem with splitting into separate tables is that you effectively require conditional joins - joining to the user-type specific table based on the user_type. This is a pain to code as SQL, and these days who cares about disk space?
尽管使用磁盘空间效率更高，但拆分成单独表的问题在于您实际需要条件连接 - 根据user_type连接到特定于用户类型的表。代码作为SQL很痛苦，而现在关心磁盘空间的人呢？
The better option is to have one user table with enough columns to store information about any user type, knowing that some columns won't be used for some user types. The "inefficiency" of having unused columns will more than be compensated for in execution speed and query simplicity.
更好的选择是让一个用户表具有足够的列来存储有关任何用户类型的信息，因为知道某些用户类型不会使用某些列。使用未使用的列的“低效率”将在执行速度和查询简单性方面得到补偿。
It's also easily extendible, in case you get another user type - it's far easier to add columns than it is to add tables, and as you added more user types, the requirement for new columns would diminish (there just aren't that many different things about a user you need to store)
它也很容易扩展，如果你得到另一个用户类型 - 添加列比添加表要容易得多，并且随着你添加更多用户类型，新列的需求会减少（没有那么多不同关于你需要存储的用户的事情）
#3
0
My way to do it would have been to create one table "Person" with the common fields, and one table for each type of user with a foreign key "person_id".
我这样做的方法是创建一个带有公共字段的表“Person”，以及一个带有外键“person_id”的每种用户的表。
In your request, you just have to join two tables with the foreign_key in order to get all data for one type of user.
在您的请求中，您只需要使用foreign_key连接两个表，以获取一种类型用户的所有数据。
How many types of user do you have ?
您有多少类型的用户？