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.Current design

  • Probably you need Groups too, e.g. all members of a team have access to a document. N.B. there are plenty of DMS already available if this is for real-world and not some kind of exercise
    – Simon
    Jun 10, 2017 at 20:10

3 Answers 3


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.

  • Thanks for the shrewd observation. For this point: "use the DocumentPermission entity to specify which roles have access to a certain Document (if the access type is role-based)", do you mean just adding a field like role_permission on the DocumentPermissions entity that specifies which role has access to the document based on the document id? And will this apply to all access types (Public, Private and Role based)? Jun 11, 2017 at 15:36
  • This would only apply to Role-based access, and one DocumentPermission record would specify that (one) role X has access to (one) document Y. A document can have multiple DocumentPermission records.
    – Glorfindel
    Jun 11, 2017 at 16:29
  • Please if it's not too much to ask, can you show me an ER diagram to illustrate this? I can't picture what you're saying. Thanks.. Jun 12, 2017 at 10:57
  • @ss_millionaire either this or this (if you go for a separate Role entity). I don't have any ER software to show the foreign keys, but I hope it's clear.
    – Glorfindel
    Jun 12, 2017 at 11:03

This design is a sound start:

  • you have users
  • users can have several roles
  • documents are related to users (I assume that a user is the owner of his documents whatever his role are or will be)
  • documents can have several access 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 DocumentPermission and UserRoles as 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 AccessPolicy table that makes the relation between an access-type and the roles which have special privileges for the related documents.
  • Thanks. and sorry for the late acknowledgement. I understand the second point perfectly. But for the first point, how do i establish that relationship between DocumentPermission and UserRoles? I'm not sure how to implement that. Jun 11, 2017 at 15:16
  • Suppose you don't implement the second point. You'll have role such as "staff" and "intern". You'll have permissions such as "public", "private", but also role based permission such as "staff only" and "intern only". So you must have an optional relation between DocumentPermission and UserRoles. You could implement this using an nullable column in DocumentPermission containing the id of the related role (implies that one such permission gives access to one role). If you implement the second point, you'll not need this, because the many to many relation allows to define it also.
    – Christophe
    Jun 11, 2017 at 15:45

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

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.