Reputation: 3760
I have a fairly common database schema for authentication/authorization:
User
- Id
- Username
- Password
Role
- Id
- Name
RolePermission
- RoleId
- PermissionType
UserRole
- UserId
- RoleId
Up to there, no problems. Now I need to associate UserRole
s to resources in a fairly complex way.
E.g. imagine a Department
resource. A user could be a Teacher for one department and an Auditor for other departments. The same user could also be a Supervisor for the entire Institute resource.
Now I need to express that requirement in my database structure and I came up with that idea:
User
Id Username
1 user1
Role
Id Name
1 Teacher
2 Auditor
3 Supervisor
Department
Id Name
1 Dep1
2 Dep2
3 Dep3
Institute
Id Name
1 Inst1
UserRole
UserId RoleId EntityName EntityId
1 1 Department 1
1 2 Department 2
1 2 Department 3
1 3 Institute 1
This could work but I'm worried by the lack of referential integrity enforcement. Are there better solutions to model that requirements?
Upvotes: 1
Views: 157