I am working with three entity types, Group, User and Item. A Group consists of Users, and a User consists of Items.
Now, I have to work with a fourth entity type: Point. I want to be able to distribute these Points on a Group, User and Item level. A Point is an entity that can be assigned on many levels.
Say I have a Point in "Group 1", then all users and items have access to that. So let's say I add a new user and attach it to "Group 1" then it would automatically have access to that point in the application because it's a member of "Group 1".
Assuming that I want to give a point to "Group A" then a point should be associated with the group, all the users and the items of those users.
If I, for example, give a point to "User 2" then the point is associated with that user and all the items associated with that user.
My idea for the three levels is dynamically adding new data and hence inheriting those permissions while having the ability to add the Point permission all the way down to a Item level if you decide that just a single item should be able to see it.
How would I go about making a data scheme for this scenario?
My attempt so far
My attempt at a diagram of how the significant entity types would connect in my head:
Example with assigning GROUP 1 to POINT 1:
INSERT INTO Point_group (group_id,point_id) VALUES (1,1);
Example with assigning USER 1 and 2 to POINT 1:
INSERT INTO Point_user (user_id,point_id) VALUES (1,1); INSERT INTO Point_user (user_id,point_id) VALUES (2,1);
And then write some logic to determine what entries are marked based on that. I feel a bit lost.