What's the best database model to use for an user registration process that deppending on the user's type selected, some fields will be required or not.
什么是用于用户注册过程的最佳数据库模型,该过程会根据所选用户的类型进行更改,某些字段是否必需。
Example: User Type A requires: Name, Email, Birth date and Gender User Type B requires: Company Name, Contact Person, Email, N. employees
示例:用户类型A要求:姓名,电子邮件,出生日期和性别用户类型B要求:公司名称,联系人,电子邮件,N。员工
I think the most commom is to create a table with all these fields and make them allow NULL.
我认为最常见的是创建一个包含所有这些字段的表,并使它们允许NULL。
What would you guys suggest?
你们会建议什么?
3 个解决方案
#1
You could create a base "User" table that holds any common fields, and other tables that hang off User with a FK to the base User.ID. If there are no common fields, it might make sense to just have different tables, rather than one table with a bunch of nullable fields.
您可以创建一个包含任何公共字段的基本“用户”表,以及将具有FK的用户挂起到基本User.ID的其他表。如果没有公共字段,那么只有不同的表可能是有意义的,而不是一个包含一堆可空字段的表。
#2
For consistency it is better to have one "main table" for users, in which the only field will be some unique user identifier that is common to users of both types (E.g., an ID, SSN or whatever).
为了保持一致性,最好为用户提供一个“主表”,其中唯一的字段将是两种类型的用户(例如,ID,SSN或其他)共有的一些唯一用户标识符。
It is then up to you how to represent the specialization.
然后由您决定如何表示专业化。
One option is to have a second field in that table representing the type, and then two more tables with details, one for each type. When you query, you'll ensure that you are only looking in the TYPE_A table for details of a user if that user has a type of A.
一种选择是在该表中有第二个字段表示类型,然后是另外两个包含详细信息的表,每个表对应一种类型。查询时,如果用户的类型为A,您将确保仅查看TYPE_A表中的用户详细信息。
A second option is to not have that field, and count on the fact that there is a record in a specific table to represent the type, but that is fairly risky.
第二种选择是不具有该字段,并依赖于特定表中存在表示该类型的记录这一事实,但这是相当危险的。
#3
I've previously done the same thing. As long as you store the customer type then it's not difficult to get the correct details.
我之前做过同样的事情。只要您存储客户类型,就不难获得正确的详细信息。
You may consider 2 tables and use views to union them if there is going to be one table that is much larger than the other, although an index on the customer type will do much the same thing. By 'much the same ting' i mean make data of one type quick to access.
如果有一个表比另一个表大得多,您可以考虑使用2个表并使用视图将它们联合起来,尽管客户类型的索引会做同样的事情。 “大致相同”意思是让一种类型的数据快速访问。
The nice thing about putting all the data in one table is you don't have to do any joins to get to the data, which helps with the speed of the query and ease of writing.
将所有数据放在一个表中的好处是您不必进行任何连接来获取数据,这有助于查询的速度和编写的简便性。
#1
You could create a base "User" table that holds any common fields, and other tables that hang off User with a FK to the base User.ID. If there are no common fields, it might make sense to just have different tables, rather than one table with a bunch of nullable fields.
您可以创建一个包含任何公共字段的基本“用户”表,以及将具有FK的用户挂起到基本User.ID的其他表。如果没有公共字段,那么只有不同的表可能是有意义的,而不是一个包含一堆可空字段的表。
#2
For consistency it is better to have one "main table" for users, in which the only field will be some unique user identifier that is common to users of both types (E.g., an ID, SSN or whatever).
为了保持一致性,最好为用户提供一个“主表”,其中唯一的字段将是两种类型的用户(例如,ID,SSN或其他)共有的一些唯一用户标识符。
It is then up to you how to represent the specialization.
然后由您决定如何表示专业化。
One option is to have a second field in that table representing the type, and then two more tables with details, one for each type. When you query, you'll ensure that you are only looking in the TYPE_A table for details of a user if that user has a type of A.
一种选择是在该表中有第二个字段表示类型,然后是另外两个包含详细信息的表,每个表对应一种类型。查询时,如果用户的类型为A,您将确保仅查看TYPE_A表中的用户详细信息。
A second option is to not have that field, and count on the fact that there is a record in a specific table to represent the type, but that is fairly risky.
第二种选择是不具有该字段,并依赖于特定表中存在表示该类型的记录这一事实,但这是相当危险的。
#3
I've previously done the same thing. As long as you store the customer type then it's not difficult to get the correct details.
我之前做过同样的事情。只要您存储客户类型,就不难获得正确的详细信息。
You may consider 2 tables and use views to union them if there is going to be one table that is much larger than the other, although an index on the customer type will do much the same thing. By 'much the same ting' i mean make data of one type quick to access.
如果有一个表比另一个表大得多,您可以考虑使用2个表并使用视图将它们联合起来,尽管客户类型的索引会做同样的事情。 “大致相同”意思是让一种类型的数据快速访问。
The nice thing about putting all the data in one table is you don't have to do any joins to get to the data, which helps with the speed of the query and ease of writing.
将所有数据放在一个表中的好处是您不必进行任何连接来获取数据,这有助于查询的速度和编写的简便性。