如何规范不同用户组具有不同类型配置文件的数据库？
我的应用程序数据库有一个组表，它将用户分为逻辑角色并定义访问级别（管理员、所有者、销售人员、客户服务等）。
组有许多用户。用户表包含登录详细信息，例如用户名和密码。
现在我希望将用户配置文件添加到我的数据库中。我遇到的麻烦（可能是由于我对正确的数据库规范化相对不熟悉）是不同的用户组有不同类型的配置文件。因此，销售人员的个人资料将包括他的佣金百分比，而管理员或客户服务则不需要此值。
那么，正确的方法是为每个组创建一个唯一的配置文件表吗？ （例如 admin_profiles 或 salesperson_profiles）。或者是否有更好的方法将某些详细信息合并到通用配置文件中，而某些用户则拥有扩展信息。如果是这样，有什么很好的例子来说明如何使用给出的佣金示例来做到这一点？
My application database has a Groups table that separates users into logical roles and defines access levels (admin, owner, salesperson, customer service, etc.)
Groups has many Users. The Users table contains login details such as username and password.
Now I wish to add user profiles to my database. The trouble I'm having (probably due to my relative unfamiliarity with proper database normalization) is that different user groups have different kinds of profiles. Ergo, a salesperson's profile will include his commission percentage, whereas an admin or customer service would not need this value.
So, would the proper method be to create a unique profile table for each group? (e.g. admin_profiles, or salesperson_profiles). or is there a better way that combines certain details in a generic profile, while some users have extended info. And if so, whats a good example of how to do this with the commission example given?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
这取决于个人资料信息的差异程度。在我们的例子中，与其他用户相比，销售人员拥有更多我们存储的信息。我们需要知道他们所在的领域、他们所在的销售队伍、他们代表的品牌等。
如果您只有一或两条不同的数据，只需添加列并使它们可为空即可。如果您有大量额外数据，您将需要其他组的销售人员数据表。这些表大多是具有一对一关系或一对多关系的表，具体取决于数据的性质。
但请将适用于所有人的一般个人资料信息保留在一处。最终，您可能会拥有具有多个角色（销售人员和管理员）的用户，因此您希望基本用户信息仅存储一次。
Well that depends on how differnt the profile information is. In our case, salespeople have many more pieces of information that we store compared to other users. We need to know the territory tory they are in, the sales force or forces thay are in, the brands they represent, etc.
If you only have one ro two pieces of differnt data, just add the columns and make them nullable. If you havea a lot of extra data, you will need tables for the salesperson data vice other groups. These many be tables with a one-to one relationship or one to many depending onteh nature of the data.
But keep the general profile information that applies to all in one place. Eventually you may have users who have multiple roles (salesperson and admin) and so you want the basic user information stored only once.
在这种情况下，这些配置文件确实是由现实世界的标准定义的，我可能会建议使用专用于每种类型的表。不过，为了充分披露，您可以使用 EAV 系统来存储这些。类似于：
Profile表定义配置文件的父表。您的每种个人资料类型在此各占一行。Criteria定义配置文件中可以存在的基本条件，无论是哪个配置文件（例如，您可能在多个配置文件中具有相同的条件）。CriteriaProfile用于在Profile和Criteria之间创建 am:m 关系。您还可以在此处添加诸如针对特定配置文件中的条件进行排序之类的内容。UserCriteria指向给定条件的用户特定值。这还允许您切换用户的配置文件，并通过删除不属于新配置文件的内容来维护任何常见的标准。然而
EAV 结构需要大量维护开销。现在您必须自己管理结构化数据，而不是依赖 RDBMS 的工具来管理结构化数据（这是人们花费大量金钱才得到的）。表往往变得非常大，比非 EAV 系统快得多，因为您的行数是其数倍（因为您现在有一行代表正常结构中的每一列）。
EAV 功能强大且灵活，但并不总是解决方案。如果您的个人资料需要动态，那么它们可能是一个合适的工具。
In a case like this where these profiles are truly defined by real-world criteria, I would probably suggest using tables dedicated to each type. In the interest of full disclosure, though, you could use an EAV system to store these. Something akin to:
The
Profiletable defines the parent table for profiles. You'd have one row in this for each of your profile types.Criteriadefines the basic criteria that can exist on a profile, regardless of which profile (for example, you may have the same criteria on more than one profile).CriteriaProfileserves to create a m:m relationship betweenProfileandCriteria. This is also where you'd add things like sorting for a criteria within a particular profile.UserCriteriapoints to a user's specific values for a given criteria. This would also allow you to switch profiles for a user and maintain any criteria that were common by just deleting those that were not part of the new profile.HOWEVER
EAV structures come with a lot of overhead to maintain. Instead of relying on the RDBMS's facilities for managing structured data (which is what people are paid a lot of money to come up with), you now have to manage it yourself. Tables tend to get very large substantially faster than with non-EAV systems, as you have many times the number of rows (as you now have a row for what would have been every column in a normal structure).
EAV's are powerful and flexible, but not always the solution. If your profiles need to be dynamic, then they can be a suitable tool.
最好的方法可能是拥有一个表
admin_profiles、一个表salesperson_profiles等等。每个表都应该有一个引用基本“用户”表的外键，并且适用于所有（或大多数）类型用户的任何属性都应该是“用户”表中的列。我假设不同角色的列表将是固定的，即系统用户不必添加新的、任意类型的角色。如果是这种情况，您可能会处于 EAV 领域，我知道这不是很有趣。
The best way of doing it probably is to have a table
admin_profiles, a tablesalesperson_profiles, and so on. Each table should have a foreign key referencing the basic "Users" table, and any properties that apply to all (or most) types of users should be columns in the "Users" table.I assume that the list of different roles is going to be fixed, i.e. that new, arbitrary types of roles won't have to be added by system users. If that were the case you'd probably be in EAV territory, which I understand isn't very fun.
其他供应商已使用的另一个选项是只有 2 个表。一个列出了定义配置文件的属性，例如磁盘空间、返回的行数等。另一个列出了哪些组具有该选项。 Oracle 做了非常类似的事情。
这使得扩展比其他方法更容易，但限制了您向结构添加新名称-值对的方式。例如，如果名称/值对中有 3 个元素，您可能会遇到麻烦。但我认为这是更容易、更干净、更可扩展、更简单的方法。
Another option, which has been used by other vendors, is to have only 2 tables. One lists the attributes that define the profile, such as disk space, number of rows returned, etc. The other lists which groups have that option. Oracle does something very similar.
This allows for expansion easier than the other methods, but limits you in how you add new name-value pairs to the structure. For example, if there are 3 elements in the name-value pair, you might be in trouble. But I think this is the easier, cleaner, more scalable, and simpler approach.
最好的选择是使用 user_meta 表来存储用户的 id、元名称（佣金或您想要存储的其他值）以及元值。
这样，您就可以添加和删除字段，而无需更改数据库，而且您也不必一直查看不同的表。
因此，您可以得到
其中用户 #10 是销售人员，用户 #50 是客户服务代表。
Your best bet is to use a user_meta table that stores the user's id, the meta name (commission or some other value you want to store), and the meta value.
This way, you can add and remove fields without ever having to change the database, and you also won't have to keep looking at different tables.
So you could have
Where user #10 is a salesperson and user #50 is a customer service representative.
如果您的组长期不会改变，我建议为每个组的每个配置文件保留单独的表 - admin_profile、salesperson_profile。以所有这些配置文件类型在用户对象本身中可用的方式设计您的对象模型。
编辑
如果您的对象模型是健全的，那么您可以执行以下操作。
使用来自 User 和 Groups 表的外部引用创建表 USerProfiles。添加两个列：ProfileFieldName 和 ProfileFieldValue。还要添加一个主键字段。在运行时，在适用的情况下，继续为每对组和用户添加 ProfileFieldName 和值。最重要的是，在对象模型的 User 对象中，提供处理 ProfileFieldName 的类型化属性。
我仍然建议按照以前的方式进行操作，因为这样做会花费更多的查询和更多的处理周期。在这种方法中，构建用户对象将花费相对更多的时间和处理。
If your groups are not going to change in long run, I'd advise to keep seperate table for each profile for each group - admin_profile, salesperson_profile. Design your object model in a way that all these profile-types are available within User object itself.
EDIT
If your object model is sound, then you can do following.
Create a table USerProfiles with Foreign references from USer and Groups table. Add two coloumns, ProfileFieldName and ProfileFieldValue. Also add a primary key field as well. At runtime, keep on adding ProfileFieldName and Values for each pair of Group and User, wherever applicable. Most importantly, in User object of your object model, provide typed-properties that address ProfileFieldName.
I'd still recommend to do it the previous way since doing it this way would cost you more queries and more processing cycles. Building User object would take comparatively more time and processing in this approach.