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.
I think your approach – in having 3 joining tables (
Point_item) is sound.
The considerations of this design are:
- Adding “permission” is easy enough – if the user selects to add to
User, then you insert into
- Starting with a
Point, if you want to ask “Which Groups, Users and Items have permission?” then you will need to construct a query against all 3 joining tables. You could have 3 separate queries and aggregate results in your application, or you could have a single query which is the
SELECTs (with a column representing the
SELECTthat includes a row, with values like
- If you want to start with a Group, User or Item, and ask “Which points are permitted for this Group/User/Item?” then your query gets more complicated the lower down you go. For example – if you start with an Item, you have to query
Point_itemfor all rows having the ID of the Item. You also have to find all linked
Userrows – and for each of those rows, check
Point_user. Likewise, you then have to find all linked
Grouprows and then search
Point_groupfor all matches. In straight SQL this will be a large
SELECT– but that may be totally fine – it’s just something to think about.
To come at this another way – what alternatives do you have?
- You could have a single table that somehow maps permissions from a Point to other objects. Its design would have
point_id, of course, and then a column to specify what it is that this Point is mapping to – ie.
Item. Then, of course, you need an
idcolumn – which would link to the relevant table.
I have seen models such as this alternative implemented before, and quite frankly, I think that’s a worse design. Such a design means that when you want to join from your Point to the other entity, you need to check this mapping flag column in order to make a decision about which table should be joined. It’s messy, ugly and performance intensive.
There is one further alternative, however:
- Merge your Group, User and Item tables into a single table. For want of a better name, call it
Entity, in this example. You now need a flag to indicate the type of entity on a given row – such as
Item. You will also want a column to specify
parent– so that for an
Itemrow you can specify its
This last suggestion would mean you only need a single mapping table between Entity and Point. However, run through all of the above scenarios again – how will you query this if you start with, say, a Point, and want to know which Groups, Users and Items have permission to view? Getting a list of all entities would be a simple query, but if you want to list all members of all entities, you would need the entity table to join itself multiple times.
This last design may have its benefits, but I think your original proposal is the easiest to maintain and understand.