Best relational database structure for this data 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.
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.