I'm designing a database for our CRM system and need some help with the CRM User table.
我正在为我们的CRM系统设计一个数据库,需要一些CRM用户表的帮助。
Types of users:
用户类型:
- Admin
- Sales Reps from Branch 2
- Sales Reps from Branch 3
- Client login
分支机构2的销售代表
第3分部的销售代表
Now for this scenario would it make sense to have all users in a single table and have a table attribute called "type" to identify the type of user? OR should I have a seperate table for each type of user? Also, there will be some information sharing between the Sales reps.
现在针对这种情况,将所有用户放在一个表中并使用名为“type”的表属性来识别用户类型是否合理?或者,我应该为每种类型的用户分配一个单独的表吗?此外,销售代表之间将共享一些信息。
3 个解决方案
#1
1
Typically, I usually go with one User table with a Type associated with it. If you have additional Sales Rep attributes you want to store, create a SalesRep table with a foreign key back to the User table. Then, create a view that joins User and SalesRep, so it looks, logically, like there's just a usvSalesRep table that has all of the attributes you need for Sales Reps.
通常,我通常会使用一个与其关联的类型的User表。如果要存储其他Sales Rep属性,请使用外键将SalesRep表创建回User表。然后,创建一个连接User和SalesRep的视图,因此它在逻辑上看起来就像只有一个usvSalesRep表,它具有Sales Reps所需的所有属性。
But, this depends a lot on data volume and transaction load, so additional information you can supply there is useful.
但是,这在很大程度上取决于数据量和事务负载,因此您可以提供的其他信息非常有用。
#2
1
It depends on the number of users that you expect.
这取决于您期望的用户数量。
But usually a single table is enought.
但通常只需要一张桌子。
If you'll have billions of users maybe you can do horizontal partitioning and make multiple tables.
如果您拥有数十亿用户,则可以进行水平分区并制作多个表格。
#3
1
Single table should be fine. And I disagree that the number users really have much impact on its design in this case.
单桌应该没问题。我不同意用户在这种情况下对其设计的影响确实很大。
Whenever possible, you should design your tables to mimick real life. Admin, Sales Rep, etc are just descriptions/attributes of who they are. Ultimately, they are all "People"... or User. So having one "User" table with "Admin", "SalesRep" as attibutes makes sense to me. Use the "Type" approach only if the user can only be one "Type". Use separate columns if they can be multiple user types. Ie. one can be a SalesRepBranch2 and SalesRepBranch3 at the same time. Might consider normalizing this even further.
只要有可能,你应该设计你的桌子来模仿现实生活。管理员,销售代表等只是他们的描述/属性。最终,他们都是“人”......或用户。因此,将一个“用户”表与“管理员”,“SalesRep”作为属性是有道理的。仅当用户只能为“类型”时才使用“类型”方法。如果它们可以是多个用户类型,请使用单独的列。 IE浏览器。一个可以同时是SalesRepBranch2和SalesRepBranch3。可能会考虑进一步规范化。
#1
1
Typically, I usually go with one User table with a Type associated with it. If you have additional Sales Rep attributes you want to store, create a SalesRep table with a foreign key back to the User table. Then, create a view that joins User and SalesRep, so it looks, logically, like there's just a usvSalesRep table that has all of the attributes you need for Sales Reps.
通常,我通常会使用一个与其关联的类型的User表。如果要存储其他Sales Rep属性,请使用外键将SalesRep表创建回User表。然后,创建一个连接User和SalesRep的视图,因此它在逻辑上看起来就像只有一个usvSalesRep表,它具有Sales Reps所需的所有属性。
But, this depends a lot on data volume and transaction load, so additional information you can supply there is useful.
但是,这在很大程度上取决于数据量和事务负载,因此您可以提供的其他信息非常有用。
#2
1
It depends on the number of users that you expect.
这取决于您期望的用户数量。
But usually a single table is enought.
但通常只需要一张桌子。
If you'll have billions of users maybe you can do horizontal partitioning and make multiple tables.
如果您拥有数十亿用户,则可以进行水平分区并制作多个表格。
#3
1
Single table should be fine. And I disagree that the number users really have much impact on its design in this case.
单桌应该没问题。我不同意用户在这种情况下对其设计的影响确实很大。
Whenever possible, you should design your tables to mimick real life. Admin, Sales Rep, etc are just descriptions/attributes of who they are. Ultimately, they are all "People"... or User. So having one "User" table with "Admin", "SalesRep" as attibutes makes sense to me. Use the "Type" approach only if the user can only be one "Type". Use separate columns if they can be multiple user types. Ie. one can be a SalesRepBranch2 and SalesRepBranch3 at the same time. Might consider normalizing this even further.
只要有可能,你应该设计你的桌子来模仿现实生活。管理员,销售代表等只是他们的描述/属性。最终,他们都是“人”......或用户。因此,将一个“用户”表与“管理员”,“SalesRep”作为属性是有道理的。仅当用户只能为“类型”时才使用“类型”方法。如果它们可以是多个用户类型,请使用单独的列。 IE浏览器。一个可以同时是SalesRepBranch2和SalesRepBranch3。可能会考虑进一步规范化。