I'm designing a database that holds the information for users of three diferentes apps that share the access to the data trough an API. For every user i'm storing credentials, profile and roles.

What I'm looking forward to have should accomplish the following requirement:

Some users need to have access to more than one app. The user will have diferent roles or permissions to access certain resources or protected views in every app. The user should be able to login into every app using a unique set of username/password credentials.

This is the schema I've come up with so far: enter image description here

Let me give a brief explanation of it:

  • Every user has access to a certain apps trough the app_access table
  • Every app is composed by a set of resources
  • A user may have one or more roles
  • Every role is composed by a set of permissions
  • Every permission specifies the actions available for that role over a resource of a specific app.

I want to know if this schema is good to accomplish what I need? Are there any know patterns for this requirement? What could be improved in my design?

  • How would your schema change if each user (as identified by a single username/password credential set) can access only a single app? It might be that the term "User" is used with different meanings (as in, a set of credentials and the person accessing the system). Nov 2, 2018 at 7:45
  • I'm not sure if I understand your question. If a user only has access to one app, that would be reflected on the app_access table. Nov 2, 2018 at 13:12
  • What I mean is that it might be possible to ignore the fact that user john in app1 and user john in app2 might refer to the same person. You might be able to treat those accounts as completely separate and independent and that might simplify your design. You will have to check with the author of the requirement if this simplification is possible. Nov 2, 2018 at 15:23
  • Don't you think that the profile related to the user defines who that person is? I can't see how ignoring that fact (ignoring the profile relation) could simplify the model. Or you may be refering to other thing and I'm not understanding. Nov 2, 2018 at 16:11
  • Oh, now I understand. You mean that I could have a repetead pair of credentials for the person John and add an app_id to the users table making tihs a users<--N:1-->apps? Nov 2, 2018 at 16:14


Your Answer

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

Browse other questions tagged or ask your own question.