I am designing a database for a document management app for my office. I want to get the backend right so I dont 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.
Role based Database Schema
database;database design;orm;postgres
One problem that I see withrole-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.