lundi 26 août 2019

Simple Database structure for access to records

My question is about rights for data access to certain information and the easiest way to check these rights in a simple structure. In my case, this applies to the best structure for a database with tables containing documents that can only be edited by certain users. My database structure is currently the following:

docs
{
  doc_id: uuid (primary key)
  name: varchar
  content: varchar
}

workgroup
{
  workgroup_id: uuid (primary key)
  name: varchar
}

workgroup_members
{
  workgroup_member_id: uuid (primary key)
  workgroup_id: uuid
  user_id: uuid
}

users
{
  user_id: uuid (primary key)
  name: varchar
}

For each document, there exist two possibilities:

  1. Only a single user can edit a documents
  2. A document can be 'assigned' to a workgroup, so that only members of that workgroup can edit the document

Now, what is the most simple yet effective way to embed this information in my tables?

My initial thought was to structure my docs table in the following way:

docs
{
  doc_id: uuid (primary key)
  name: varchar
  content: varchar
  workgroup_id: uuid
  user_id: uuid
}

But then, either workgroup_id or user_id is always NULL. Also, the query to check if a user with a certain ID can edit the documents is not very straightforward, and also listing all documents a user works on becomes quite complex.

I also thought of not changing the docs table, but creating a doc_access table:

doc_access
{
  doc_access_id: uuid (primary key)
  doc_id: uuid
  user_id: uuid
}

This however, would create a lot of 'duplicate' information when a document is assigned to a workgroup, because all of the workgroup members need to be given access to the document. Also, I would need to run a query every time after document creation to add this 'extra' information to the doc_access table. Checking access with this table is easier though.

Is there maybe a better/simpler way to store this relationship between users, workgroups and document access?

Aucun commentaire:

Enregistrer un commentaire