Current design
I have a Person model:
PersonId Name
----------- ----------------
1 Jessica
2 Jennifer
Then I have a ShareModel which have the Personand an Itemassociated.
ShareId ItemId
----------- ----------------
1 1
2 2
And finally I have a SharePeoplewhich tells all people that have access to that Item.
ShareId PersonId
----------- ----------------
1 1
1 2
New feature
Now a new feature must be added, which is Groups. So I thought about two aproaches and I would like to know which one is the more correct.
There are two things I see clearly:
-
add a new
IsGroupproperty to thePersonModel.PersonId Name IsGroup ----------- ---------------- ----------- 1 Jessica False 2 Jennifer False 3 Blondes True -
create a
GroupPeopletableGroupPersonId ChildPersonId ------------- ---------------- 3 1 3 2
The questions
OPTION 1 : Should I create the association on the SharePeople with the IsGroup rows directly:
ShareId PersonId
----------- ----------------
2 3
OPTION 2 : should I create the association always with the persons which are part of the group, and have another column on that will identify from which Group the association was made.
ShareId PersonId GroupPersonId
----------- ---------------- -------------
2 1 3
2 2 3
The second option is more simple when getting data from tables, it will be fast and will create less impact, however it creates a lot of rows on the database.
The first option is cleaner but I will have to do a lot of logical statements inside my code wheter the row it’s a Group or a single Person.
But bottom line is that I really don’t know what’s better concerning performance, and I also don’t whant to do tons of odd code.
Thank you very much,
For Option 1, you appear to be missing the data about which people are in each group, and now every query on the Person table has to know about these fake “group people”, even ones that don’t care about sharing or groups.
Option 2, by tying the person->group mapping to the share->person mapping (which really have nothing to do with each other), is effectively making the assumption that each person is part of at most one group. That’s probably not a safe assumption.
Somewhere, there needs to be a table mapping peopleIds to groupIds, so you know who is in each group. Since this is probably a many-to-many relationship (a group can have N people, and a person can be in N groups), this should be a completely separate Groups table, not part of the Person or the Share tables.
This does mean your sharing/grouping queries will have more joins, but joins aren’t hard, and a correct query that understandings grouping is many-to-many is much better than a simple but wrong query. Don’t worry about performance until you have a measurable problem, and even then you should focus on standard database optimization features like indexes and replication (which are far more likely to improve performance than butchering your schema design).
1
Don’t add isgroup to your person table. Make a group table as its own entity.
Role-based security model: person, role, group, roleonGroup, roleonUser, permission, itemPermission, itemPermissiononRole
or
person, role, group, roleonEntity, permission, itemPermission, itemPermissiononRole
where ‘entity’ can be a person or group. I prefer the former even though it requires more code.
I am suggesting itemPersmission instead of Share because you can have more than one permission on an item, e.g. create item, read item, write item, delete item, which can then be assigned by role.
You can adapt the names to your model, but the idea is that Share is a permission and does not need its own entity.
If this sounds like overkill for your needs, just consider what would happen if your requirements changed and in addition to Share, you had Modify. Very quickly your schema and code would get out of hand.