Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

ERD Diagram

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.

share|improve this question
    
I'm not sure I've wrapped my mind around the interplay of roles, applications, and permissions. Each role/application has a specific permission that is independent of a role's other applications? For example, a CEO/Topdesk might have only read permission, while a CEO/Calendar might have write? –  mdoyle Mar 18 '13 at 21:34
    
I don't understand what you mean "best relational database schema for this data" do you mean what engine? Like Postgres or MySQL or MSSQL or ... etc? –  jcolebrand Mar 19 '13 at 2:45
    
@jcolebrand Guess the title is even less clear than before... Perhaps "database structure" is a better word. The table layout and relations (foreign keys, etc.). –  Luc Mar 19 '13 at 8:46
    
@mdoyle Permissions are always within an application (example: permission 'change the system clock' in application 'windows') and thus a permission belongs to exactly one application. A role can have both permissions and applications, the only constraint being that if you have any permissions, you must also have access to the application to which the permissions belong. (Obviously if you can't use Topdesk, you can't have permission 'Update knowledgebase' for application Topdesk.) Does this make it clearer? –  Luc Mar 19 '13 at 8:52
    
I'm not convinced that Roles_Applications is a real thing. Given that all permissions are application-specific, it seems there are Applications_Permissions (what you have labeled Permissions), which may then be assigned to specific roles via a record in Applications_Permissions_Roles. Roles_Applications, in fact, seems to be describing the most basic application permission--simple access. Or, it is asserting that a role has some level of permissions for an application, but you have to look elsewhere to determine which ones. –  mdoyle Mar 19 '13 at 13:45
show 2 more comments

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks for your response! I hadn't thought of these suggestions yet. The second suggestion would add a lot of application logic though: all places that handle permissions should check if an operation is not performed on the default permission (since that should not be mutable or perhaps even shown); and stuff dealing with applications should make sure the default permission is created or removed appropriately. It would simplify the database, but at a higher cost it seems. The first suggestion is an option though, I think I'll implement this. Thanks again for the review :) –  Luc Mar 19 '13 at 15:13
add comment

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.

share|improve this answer
    
Okay, I see what you mean now. The issue is that one does not always have permissions on an application. Like with Microsoft Word, there simply are none. In those cases it's unknown which role can access which application; you must always have at least one permission from that application assigned to a role. That's what the "default permission" suggestion from Joel Brown was for. Thanks for the suggestion though! It's not a bad idea and makes sense, but I simply can't apply it to my situation. *Upvoted* –  Luc Mar 19 '13 at 21:17
    
For something like Word, isn't "execute" a permission? But I see your point if for some reason executing or accessing an application isn't considered a permission. –  mdoyle Mar 19 '13 at 21:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.