We have multiple applications with different features. We want to create a central security system, where each user is assigned a role. Role has access to certain applications and features with in those applications. What is the best way to define this in database with good normalization.

We want to identify the roles, and applications they have access to and features with in those applications they have access to ahead of time, so user is only given a role and by default they get everything that role has privileges to.

  • 1
    Sharing your research helps everyone. Tell us what you've tried and why it didn't meet your needs. This demonstrates that you've taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. Also see How to Ask
    – gnat
    Commented Apr 12, 2020 at 13:26
  • I tried designing, user table, user role table, application table, application to features table, combining user role to application to features table and in the users table will have foreign key to user role table. Is there a flaw in this design ?
    – tamilgirl
    Commented Apr 12, 2020 at 13:48
  • @tamilgirl Edit your question to provide this information. Don't bury it in comments. Commented Apr 12, 2020 at 13:54

1 Answer 1


A foreign key in the User table to indicate the Role means that one user can have only one role:

  • If this is what you want, it's fine.
  • However, in real life, it is much more flexible to allow several roles. Even if 90% users have a single role, imagine that a user fells ill and has to be replaced on the spot by another one. It's much easier to just assign an additional role, than to create a new combo-role with hundreds of features.

If you go for the multiple roles, you just need to use an association table: an additional table has a foreign key to User and a foreign key to Role.

It is not completely clear how you relate roles fo application and features. So here a commented UML diagram how you could do it (assuming that you need a table for documenting the applications, and another table for documenting the available features:

enter image description here

Edit based on the ER schema that you provided

  • User_roles table in your diagram is in fact an association table between Users and Roles (in your first explanation, I thought that user_roles was in fact the foles).
  • The added value of the Role_to_application is not clear. If makes sens if the role would give some feature-independent authorisations in the application. If not, you coud consider removing it and instead replace in Role_application_to_feature the column Role_application_id with RoleId to merge the two.
  • Role_application_to_feature table in your diagram is an association table between the role the application and the feature.

So, although there is some possible simplification, your schema looks ok.

P.S: Next time, try to provide the schema from the start: you'll get a more precise anwser faster ;-)

  • Christophe, can you explain the yellow association table with example? I am new to this database design and trying to normalize and make it more scalable, thank you for your input
    – tamilgirl
    Commented Apr 12, 2020 at 15:15
  • Yes I want to have a table for applications, and a table for application with associated features. I want association table where I associate User role to applications and features they have permissions to. This association table i am not clear, should I do user to applications as one table and user to applications to features as another table ?
    – tamilgirl
    Commented Apr 12, 2020 at 15:22
  • @tamilgirl The yellow is not association tables, it’s comments to the classes/tables in white and the association (lines). An association table is a table that allow to join two other tables: it implements a many-ti-may relation between tables. It’s just a table with two foreign keys as column: one foreign keybfor each associated table.
    – Christophe
    Commented Apr 12, 2020 at 16:13
  • I have added by ER database diagram so far, can you review that and let me know your comments please
    – tamilgirl
    Commented Apr 12, 2020 at 16:44
  • @tamilgirl I edited my answer
    – Christophe
    Commented Apr 12, 2020 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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