I am designing a database for a document management app for my office. I want to get the backend right so I don’t have issues in the future. Basically all users will be categorized into roles e.g, super admin, admin, staff and interns. All documents will have access restrictions e.g public(all user roles can view the document), private (only user who created it can view), role-based(this means only users with the same roles can view these documents). However, Admins and super admins can view all documents. One user can have many documents. Also user roles can be updated e.g from staff to admin etc. I will be using Postgres with Sequelize as my ORM for this project. Based on the description above, is this a good design? What can be improved? Any inputs will be appreciated.
1
One problem that I see with
role-based (this means only users with the same roles can view these documents)
is that if I create a document with this type as an intern, and later get promoted to a staff position, will that document be visible to the interns or to the staff?
I’d rather set the access_type field on the Document itself, and use the DocumentPermission entity to specify which roles have access to a certain Document (if the access type is role-based).
Also (but that is my opinion, I’ve seen more implementations like yours), I’d put the roles (superadmin, admin, staff, intern) into a separate entity, and have the UserRole entity link to that entity instead of the role_name column. Your design might save you a join, but in the end it’s less flexible.
4
This design is a sound start:
- you have
users userscan have severalrolesdocumentsare related to users (I assume that a user is the owner of his documents whatever his role are or will be)documentscan have severalaccess types
But I think it is incomplete. These two things are missing:
- for sure: you said you wanted the possibility of having role based accesses, i.e. give permissions on a document to specific roles. This means that you should have an optional relation between
DocumentPermissionandUserRolesas well. - may be: unless you want to handle “public” and “private” access and their exceptions for administrators and super-users (and may be later for auditors) in a hard coded way (how are these special roles identified, by the way ?), you’d need a
AccessPolicytable that makes the relation between an access-type and the roles which have special privileges for the related documents.
2
As per my knowledge we should give permission to roles not to documents or users .As per ur requirement in future if you add any new role and tag it to users base u have it should work. RBAC algorithm google it u can find n no.of docs on it