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.
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.
This design is a sound start:
- you have
userscan have several
documentsare related to users (I assume that a user is the owner of his documents whatever his role are or will be)
documentscan have several
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
- 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.