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:
- Only a single user can edit a documents
- 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