DB design for tree structure of user access permissions
2
votes
0
answers
183
views
I am trying to come up with a good database design for the following problem:
We have an application where users can create objects (such as notes) and link those objects to other objects, projects or users. Projects consist of assigned objects and a set of users who can access the project.
How should we model the database to efficiently query for all objects that a user has access to, as well as for all users that have access to a given object? Access to an object is granted if one or more of the following conditions is met :
1. The user is the creator of the object
2. The object has been linked (shared) with the user
3. The user is a member of a project that the object is assigned to
4. The object is directly or indirectly linked to another object that the user has access to according to rules 1-3.
I consider using a graph database, since the amount of SQL queries when using a relational database would not be feasible. We would need recursive SQL queries.
Expected data dimensions are: 100-500 users, 100 projects and 100000-999999 objects
Asked by hg.m
(21 rep)
Mar 10, 2023, 11:25 AM