将角色作为用户表中的一列更好，还是通过连接表（角色和分配）来实现？ - 轨道3
您可以在这里看到我的模型：
https://gist.github.com/768947
只是为了解释什么是发生了，我有几个模型。我的应用程序的核心有：项目、阶段、上传、评论、用户。然后是角色和角色。分配来管理用户授权。
我正在使用插件 declarative_authorization & 来执行此操作设计登录。
所以第一个问题是，在我的用户模型/表中添加一列“角色”并在那里存储每个用户的角色是否更好？如果我有一个具有多个角色的用户，那么我可以将所有角色存储为一个数组，并根据需要循环访问它们。
还是像我现在设置的那样更好，我使用两个单独的表和一堆联接来设置分配？我只有 4 个角色：设计师、客户、管理员、超级用户。
从计算资源的角度来看，使用列执行每个查询比使用联接执行“更便宜”，从某种意义上说更好，或者差异不是那么显着？
我想，我的问题的根源是......现在，如果我想将一个项目分配给 current_user，我只需执行 current_user.projects.each do |project| 并以这种方式循环它们。这是我在项目控制器中完成： @projects = current_user.projects 之后的事情。这同样适用于我的所有其他模型 - 除了“用户”和“用户”之外。角色。
但是，如果我想找到角色为“客户”的用户，它很快就会变得很复杂。或者我把它过于复杂化了？
任何帮助将不胜感激。
You can see my models here:
https://gist.github.com/768947
Just to explain what is happening, I have a few models. At the core of my app, there are: projects, stages, uploads, comments, users. Then there are roles & assignments to manage user authorization.
I am doing this with the plugin declarative_authorization & devise for login.
So the first question is, is it better to just add a column of 'Roles' to my user model/table and store the roles for each user there? If I have a user that has multiple roles, then I can just store all the roles as an array and cycle through them as needed.
Or is it better to do it like I have it setup now, where I use two separate tables and a bunch of joins to setup the assignments? I only have 4 roles: designer, client, admin, superuser.
Better in the sense that it is 'less expensive' from a computing resources standpoint to do each query with the column, than with the joins or is the difference not that significant?
I guess, the root of my question is...right now if I want to get a project assigned to the current_user I simply do current_user.projects.each do |project| and cycle through them that way. This is after I have done: @projects = current_user.projects in the projects controller. The same applies for all my other models - except Users & Roles.
However, if I wanted to find a user with role "client", it becomes convoluted very quickly. Or am I overcomplicating it?
Any help would be appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我认为最好将用户表和角色表分开。这是一种多对多关系，因为一个用户可以拥有多个角色，并且多个用户可以拥有相同的角色。您需要一个 JOIN 表（例如 user_role）来执行此操作。我推荐三张桌子。当然，您将拥有用户和角色的主键； user_role 表将有两列，每一列对应主键，以及与其各自表的外键关系。
因此，现在如果您希望所有用户都具有“client”角色，则可以在 user 和 user_role 之间轻松加入。如果您想要特定用户的角色，则需要联接这三个表。
我不会推荐用户中的一系列角色。它违背了第一范式。
I think it's better to have user and role tables that are separate. It's a many-to-many relationship, because a user can have many roles and many users can have the same role. You'll need a JOIN table (e.g. user_role) to do that. I'd recommend three tables. Of course you'll have primary keys for both user and role; the user_role table will have two columns, one for each primary key, and foreign key relationships to their respective tables.
So now if you want all users with the role "client", it's an easy JOIN between user and user_role. If you want a particular user's roles, you'll need to JOIN the three tables.
I would not recommend an array of roles in user. It goes against first normal form.