0

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?

Francisco Hanna
  • 109
  • 3
  • 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). – Bart van Ingen Schenau Nov 02 '18 at 07: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. – Francisco Hanna Nov 02 '18 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. – Bart van Ingen Schenau Nov 02 '18 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. – Francisco Hanna Nov 02 '18 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`? – Francisco Hanna Nov 02 '18 at 16:14

0 Answers0