批评我的身份验证系统数据库架构？ [英] Critique my auth system DB schema?
问题描述
我设计一个基于REST的Web应用程序，将其他几个应用程序提供的认证系统。其他的应用程序将通过HTTP查询这个应用程序，并取回XML描述身份验证的用户。
I'm designing a RESTful Web app that will provide an authentication system for several other apps. The other apps will query this app through HTTP and get back XML describing authenticated users.
身份验证的应用程序需要保持的哪些用户允许的轨道做什么在的应用程序。
The authentication app needs to keep track of which users are allowed to do what on which applications.
我工作了DB模式。下面是我最初的设计。 （假定每个表都有一个 ID 列。）
I'm working out the DB schema. Below is my initial design. (Assume each table has an id column.)
applications # The various client apps that will query this auth system.
------------
name
users # Table simplified for discussion
-----
username
password
email
roles
-----
name
application_id
roles_users
-----------
role_id
user_id
我们的想法是说，有人试图在装备清单应用程序执行管理功能。因此，设备清单要说到身份验证系统获得通过用户名xxx和密码YYY用户。那就看看返回（通过的ActiveResource）用户对象，并检查其是否角色阵列包含一个角色以ADMIN的名称本身属于一个应用与设备清单的名字对象。
The idea is say someone tried to perform an administrative function in the the "Equipment Inventory" app. So "Equipment Inventory" would say to the auth system "get the user with username xxx and password yyy." Then it would look at the returned (via ActiveResource) User object and check whether its roles Array contains a Role with a name of "ADMIN" that itself belongs to an Application object with a name of "Equipment Inventory".
也许会更好，以消除应用表，并有更多的角色，如 equipment_inventory_admin ， equipment_inventory_readonly ， job_tracker_admin 等。
Or perhaps it would be better to eliminate the applications table and have many more roles, e.g., "equipment_inventory_admin", "equipment_inventory_readonly", "job_tracker_admin", etc.
什么是更重要的，标准化的应用实体或简化表结构？也许所有的打字后，我只是回答了我自己的问题，但意见或建议将是最受欢迎的。
What's more important, normalizing the Application entity or simplifying the table structure? Perhaps after all that typing I've just answered my own question, but comments or suggestions would be most welcome.
推荐答案
架构看起来神志清醒，
你会送
The schema looks sane, You would send
<$c$c><login><username>abc</username><password>xyz</password><app>51</app></login>
和你回来
＆LT;＆权威性GT;
＆lt;使用者＆GT;
＆lt;用户名＆GT; ABC＆LT; / A＆GT;
＆LT; lastlogin＆GT; 123456464＆LT; / lastlogin＆GT;
＆LT; /使用者名称＆gt;
＆LT;应用＆GT;
＆LT;名称＆gt;设备库存和LT; /名称＆gt;
＆LT;＆版GT; 3.1.5e＆LT; /版本＆GT;
＆LT; /应用＆GT;
＆LT;角色和GT;
＆LT;＆作用GT;联系＆LT; /角色＆GT;
＆LT;＆作用GT;经理＆LT; /角色＆GT;
＆LT;＆作用GT; dataentry＆LT; /角色＆GT;
＆LT; /角色＆GT;
＆LT; / AUTH＆GT;
或
＆LT;＆权威性GT;＆LT;错误类型=1＆GT;＆LT; / AUTH＆GT;
这篇关于批评我的身份验证系统数据库架构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
