Best relational database structure for this data

Question

I'm in the process of creating a database scheme for the following scenario:

• There are users
• Users have roles (such as "Developer" or "CEO")
• Roles have applications (such as "Topdesk")
• Applications have permissions (such as "Update Knowledgebase")
• A role can have permissions, if the role already has access to the application

Assuming no high-performance environment (no need to optimize for speed), what would be the best way to implement this schema? The database environment can be MySQL, MSSQL... it's more about the relational database design.

I myself have come up with the following:

The part I'm most uncertain about is of course the Applications_Permissions_Roles table. It is a linking table on top of another linking table. I've never used or seen this before. Another way to do it would be to replace it with a linking table between Roles and Permissions, and then use code or constrains to ensure the required relations... but that doesn't seem like a good solution to me. These things should be enforced on database-level if at all possible (and it seems possible), not on code-level.

Secondly, is the link between Permissions.Application and Applications.Id required? I use it because there may not be any rows in Roles_Applications (such as when you've just added a new application) and then it's not possible to work out which permissions belong to which application. It also is a single point of reference to lookup to which application a permission belongs. I guess this is right, but it also makes a circle in the database design. MSSQL errors on it when trying to set ON_DELETE or ON_UPDATE to cascade.

Any suggestions, or is this how it's supposed to be done? Any other suggestions regarding naming convention and such are also welcome by the way (perhaps as comment).

Thanks,
Luc

Edit: Changed title, hopefully making it clearer. The previous one was more comprehensive, but probably too convoluted.

Answer 1

The way you've modelled it is fine. Your model ensures that the business rules are enforced by the database.

There are a couple of things you could do as an alternative. One would be to eliminate the surrogate key on Roles_Applications. As an intersection table, you could use the two foreign keys together as a composite primary key. If you did this, that would propagate Role and Application down to your Applications_Permissions_Roles table. This would have the advantage of giving you more of a "one stop shop" for your application permission data (i.e. fewer joins) without compromising your normalization in any way.

Another way you could go would be to simplify slightly and define a default permission for each application. You could call it whatever you like, such as "default access" or "basic access" or "user" or whatever makes sense to you. This would allow you to flatten your model and essentially drop the Roles_Applications table and join Applications_Permissions_Roles straight to Roles. This would change the nature of the query that you would use to ask "which roles can access which applications?" but your business rules would still be enforced by the schema.

Answer 2

Roles_Application does not appear to represent a real thing here. What is it, beyond signifying that a particular role has some level of permissions to an application, even though you need to check out Applications_Permissions_Roles to determine what that level is? As an example, here is a CEO getting write access to the application Calendar in your current model:

Roles

ID    Name
--    ----
1     CEO

Applications

ID    Name
--    ----
C     Calendar

Permissions

ID    Application_ID  Permission
--    --------------  ----------
10    C               Write

Roles_Applications

ID    Role_ID    Application_ID
--    -------    --------------
50    1          C

Applications_Permissions_Roles

Role_Application_ID    Permission_ID
-------------------    -------------
50                     10

I think this series of relationships can be modeled without the Roles_Applications. If we remove that (as Joel Brown suggests, but without the change of assuming there are "default permissions"):

Roles

ID    Name
--    ----
1     CEO

Applications

ID    Name
--    ----
C     Calendar

Applications_Permissions (nee Permissions)

ID    Application_ID  Permission
--    --------------  ----------
10    C               Write

Applications_Permissions_Roles

Role_ID    Application_Permission_ID
-------    -------------
1          10

The old Permissions table doesn't have simply permissions, like "read" and "write", which can then be applied to applications like "Topdesk" and "Calendar": It contains application-specific permissions, like "write in Topdesk" and "write in Calendar" and "Change system clock in Windows". To make that clearer I've named the table Applications_Permissions but of course that's not a necessary step.

This approach has the effect of flattening the model, as does Joel's second suggestion, but without adding application logic or the concept of default permissions. Roles_Applications wasn't bringing anything to the party other than an indication that a role has some level of access to an application. That information is conveyed with more brevity by the existence of a record in Applications_Permissions_Roles with the proper value of Role_ID.