说服怀疑者！为什么我需要“角色”？我的数据库中的表？
在设计用于存储简单用户/角色信息的数据库表时，任何人都可以告诉我为什么将角色信息直接存储在用户表上（例如，在角色列中以逗号分隔）是一个坏主意。
想法：
- 数据库不需要了解角色，这是 UI 的域
- 访问特定用户的角色越快越好
- 当然，如果将来某个时候我想要访问特定角色的所有用户，则查询可能
有点慢，但谁在乎呢？
这有什么意义吗？我疯了吗？创建 Roles 和 UserRole 表会不会太过分并且会增加不必要的 SQL 和代码开销？
更新：
为了进一步说明我的观点......在代码中，我想知道用户“Steve”是否处于“管理员”角色。
选项 1：查询 UserRole 表以获取用户“Steve”的角色列表。循环浏览该列表并查看 RoleName 是否与“Administrator”匹配。
选项 2：拆分用户角色属性中的 csv，然后查看结果列表是否包含“管理员”
更新 II：
我同意我的建议违反了各种“最佳实践”类型的思维，特别是围绕数据库设计。然而，我不明白“最佳实践”在这种情况下有什么意义。我确实喜欢时不时地改变最佳实践......我喜欢以一种看起来聪明的方式编码，这意味着有时我需要了解更多才能知道我什么时候不聪明:)
When designing the database tables for storing simple User/Role information, can anyone tell me why it would be a bad idea to store the Role information directly on the User table (for example, Comma-Separated in a Roles column).
Thoughts:
- The database doesn't need to know about the roles, that's the UI's domain
- The quicker the access to a specific user's roles the better
- Sure, if sometime in the future I want access to all of the users for a specific role the query might
be a little slow, but who cares at that point?
Does this make any sense? Am I off my rocker? Wouldn't creating Roles and UserRole tables be overkill and add unnecessary sql and code overhead?
UPDATE:
To further illustrate my point... in code, I want to know if user "Steve" is in role "Administrator".
Option 1: query the UserRole table for a list of roles for user "Steve". Loop through that list and see if the RoleName matches "Administrator".
Option 2: split the csv in the User's Role property and see if the resultant list contains "Administrator"
UPDATE II:
I agree that my suggestion violates all sorts of "best practice" type thinking, particularly around DB design. However, I am not seeing how the "best practices" make any sense in this sort of scenario. I do like to rock the best practices boat now and then...I like to code in a way that seems smart, which means sometimes I need to understand more to know when I'm not being smart :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
因为它违反了第三范式。您希望将所有实体分隔为不同的对象，这意味着您需要一个单独的表以及一个关系表。
数据违规
如果您将所有这些内容都保存在一个表中，则会在用户表中放置太多与用户无关的信息。用户的表应该包含仅与该用户相关的字段，例如他们的姓名、用户帐户等。但在本例中，您决定添加一些角色信息。这是没有意义的，因为您添加的属性不一定与用户有任何关系。
结果是您开始添加不属于用户的字段，最终会得到大量不相关的信息。解决这个问题的方法是拥有一个像这样的用户表：
更新/插入数据
如果将角色信息存储在用户表中，则必须处理的下一个问题是如何执行有效的更新和插入。这使得事情变得更加困难。编辑记录时，您必须确保编辑 CSV 中的正确值。
找到合适的角色
这里存在最困难的问题，如何找到给定用户的角色。您可能会在 C# 或 SQL Server 中想出这种出色的解析技术，它效果很好......但它变得非常慢且难以阅读。您开始处理
SubString()、Left()、Right()、Len()和整个许多其他功能只是为了解析用户的角色。解决方案
您可能认为现在将所有内容放入一张表中会更容易。前期时间可能会少很多。但您在开发应用程序时必须着眼于未来。如果遵循 3nf 规则并创建良好的关系结构，UI 将会更加简单。 UI 管理屏幕不仅看起来不错，而且与解析或搜索相比，获取特定 UserID 的角色也非常简单......
Because it violates 3rd normal form. You want to seperate all of your entities as different objects which means you need a seperate table, as well as a relationship table.
Violation of data
If you keep all of this in one table you are placing too much irrelevant information about a user in a user's table. A user's table should have fields that pertain only to that user, such as their name, user account, etc. But in this case you've decided to throw in some role information. This doesn't make sense as you are adding attributes that do not necessairly have anything to do with a user.
The result is you start adding fields that do not pertain to a user, and you end up having a ton of unrelated information. The solution to this is have a users table like so:
Updating / Inserting of data
The next issue you have to deal with if you store role information inside of a user's table is how to perform valid updates, inserts. This makes it all the more difficult. When you edit a record, you have to ensure you edit the right value from the CSV.
Finding the right role
Here lies the most difficult problem, how to find the role given a user. You might come up with this great parsing technique in C# or SQL Server, it works great..but it becomes terribly slow and hard to read. You start dealing with
SubString(),Left(),Right(),Len()and a whole slew of other functions just to parse out the role of a user.The Solution
You may think that putting it all into one table is easier right now. It probably will take a lot less up front time. But you have to develop applications with the future in mind. The UI will be much simplier if you follow the rules of 3nf and create a nice relational structure. Not only will the UI admin screens look nice, but getting a role for a specific UserID will be so trivial, as opposed to parsing or searching...
我不认为这是一个伟大的计划。假设您正在手动更新某人的角色，并且您输入的角色名称略有错误？如果您有一个单独的表，数据库约束会警告您。假设您决定更改角色的名称？如果您有一个单独的表，则只需在一个位置进行更改。
数据库标准化是有充分理由的；这不仅仅是挑剔。您不会在多个地方重复代码库中的关键代码；数据库非规范化是等效的。
编辑添加：
您指出应用程序最终将根据数据库返回的值做出决策；例如，如果用户具有名为“管理员”的角色，则授予某些选项。这是事实，但这是另一个独立的地方，例如角色名称的一致性可能会出现问题。我不认为对数据库进行非规范化会降低这种可能性。
帮助解决此问题的一个好方法（也是一般实现授权的好方法）是在代码中拥有一个位置，其中角色被转换为某些通用能力（例如，管理员可以读取和写入所有实体，来宾可以读取某些实体）并且不能写任何东西等等）。然后，在许多需要建立访问权限的地方，您可以检查能力，而不是检查角色。
也就是说，在视图中，如果您决定是否在项目描述上显示“编辑”按钮，则不需要通过执行
if role=='ADMIN' 或 role== 进行检查“EDITOR”，您可以通过执行if user.can_edit(item)进行检查。在其他地方，您已经确定管理员和编辑者能够编辑项目。例如，请参阅 Rails 授权系统 CanCan 使用的方法。使用这种方法，只有一个地方可以引用角色的名称（例如，在 CanCan 中，您有一个名为“能力”的类，它根据角色定义了谁可以做什么的所有规则。在其他地方，您都可以使用此方法）参考用户拥有哪些能力来确定他们可以做什么或可以看到什么。
I don't think it's a great plan. Suppose you're manually updating someone's roles and you type the name of a role slightly wrong? If you had a separate table, a database constraint would warn you. Suppose you decide to change the name of a role? If you had a separate table, you would only need to change it in one place.
Database normalization is done for good reasons; it's not just nitpicky. You wouldn't repeat key code in your codebase in more than one place; database denormalization is the equivalent.
EDITED TO ADD:
You make the point that the application is ultimately going to make decisions based on the values returned by the database; e.g. granting certain options if the user has a role called "Admin". This is true, and it is another, separate place where the consistency of, for example, role names can go awry. I don't think denormalizing the database makes this less likely.
One good approach to help with this (and a good way to implement authorization in general) is to have a single location in code where the role is translated into certain general abilities (e.g. admins can read and write all entities, guests can read certain entities and can't write anything, etc.). Then, in the many places where you need to establish access, you check against an ability, rather than checking against a role.
That is to say, in a view, if you're deciding whether to show the "edit" button on the description of an item, you don't check by doing
if role=='ADMIN' or role=='EDITOR', you check by doingif user.can_edit(item). Somewhere else you've established that admins and editors get the ability to edit items. See, for example, the approach that the Rails authorization system CanCan uses.Using this approach, there's only one place where you're referencing the names of roles (e.g. in CanCan you have a class called "abilities" which defines all of the rules for who can do what, based on their roles. Everywhere else, you reference what abilities a user has to determine what they can do or see.
此外，您的第一个假设并不适用于所有情况。如果我的数据库需要了解角色怎么办？
Also, your first assumption does not hold for all situations. What if MY database need to know about roles?
这取决于您是希望数据库管理系统来管理数据还是希望应用程序来管理数据。在大多数情况下，DBMS 做得更好，因为这就是它的构建目的。
It depends on whether you want the Database Management system to manage the data or you want the application to manage the data. In most cases, the DBMS does a better job, because that's what it's built for.
你说
只有当您能够如实地说：“我百分百确定每个程序员和每个数据库管理员都会手动完美地保留数据完整性，无论什么应用程序接触此数据库，无论它是用什么语言编程的，无论它是什么，您都可以可靠地做到这一点。无论程序员是多么有经验或缺乏经验，无论数据库变得多么复杂，从现在到其生命周期结束。”
如果您在职业生涯中管理一个编程项目，那么您工作的一部分就是将编码职责分配给最适合执行这些任务的人员。在架构级别，有人还将程序职责（例如数据完整性）分配给最适合执行这些职责的模块或子系统。数据完整性只能通过将责任分配给数据库管理系统来保证。将其分配给系统的任何其他部分，无论是代码还是人类，都只是一种希望的表达，而希望并不能很好地扩展。
You said
You can do that reliably only if you can truthfully say, "I'm 100% certain that every programmer and every database administrator will manually preserve data integrity perfectly no matter what application touches this database, no matter what language it's programmed in, no matter how experienced or inexperienced the programmer is, and no matter how complex the database becomes, from now until the end of its life."
If you get to the point in your career where you manage a programming project, part of your job will be assigning coding responsibilities to the persons best suited to carry them out. At the architectural level, someone also assigns program responsibilities--like data integrity--to the module or subsystem best suited to carry them out. Data integrity can only be guaranteed by assigning that responsibility to the dbms. Assigning it to any other part of the system, whether code or human, is just an expression of hope, and hope doesn't scale well.