Sample Header Ad - 728x90

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