0

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:

  1. add a new IsGroup property to the Person Model.

    PersonId    Name             IsGroup
    ----------- ---------------- -----------
    1           Jessica          False
    2           Jennifer         False
    3           Blondes          True
    
  2. create a GroupPeople table

    GroupPersonId    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,

1

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).

  • For each options I would have the GroupPeoplelike I described. And for option 2, when a share is done directly with one person, I will have the GroupPersonId Nullable. Does it make sense? Thanks – Christopher Bailey Apr 12 '15 at 10:29
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.