This question will also be posted in the stackoverflow, since dba and programmers may have different opinions.

First of all sorry for the long question, but I wrote some extra code so that I can demonstrate my point a little better.

Data Model

Basically, it's an administration platform that each administrator can add content and users to their mobile platform.

Each administrator is responsible for his own mobile application, so users of his mobile application should not be able to see the content from another administrator and neither from items that he does not have permission for.

Data Model

I have to create user permissions so that users in my mobile app can get access to these items (Video, Audio, News, etc). Some items should not be open to everyone, let's say that there is a video that should be open for just a small group of people.

So I thought about 3 options that I can implement in SQL Server:

1) Create User and User_Permission tables for each item:

CREATE TABLE User (
    UserID INT IDENTITY,
    AdminID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    ...
);

CREATE TABLE User_Permission (
    UserID INT NOT NULL,
    ItemID INT NOT NULL,
    CONSTRAINT FK_userpermission FOREIGN KEY (UserID) REFERENCES User(UserID)
);

Pros:

  • Just have to maintain one permissions table (User_Permission).
  • Just need to request information from User_Permission to know about user permissions.
  • Custom permissions for each user.

Cons:

  • Generic ItemID where I would not know where it came from without looking for its ID in each item table.
  • No Foreign Keys.
  • ItemID could repeat (I could change VideoID/etcID to UNIQUEIDENTIFIER, but there still a very small chance that it could be the same).
  • User_Permission could get enormous, thinking that each user could have 50+ permissions to access videos, 50+ for images, etc and the platform is expected to have 50.000+ users, it could easily get up to 5.000.000+ rows.

2) Create a Group table for User and a Group_Permission table, where users would be registered in the Group table and permissions would just be for groups, not for users.

Basically, it will have the same tables as the first implementation, but would be much smaller.

CREATE TABLE Group_Permission (
    GroupID INT NOT NULL,
    ItemID INT NOT NULL,
    CONSTRAINT FK_grouppermission FOREIGN KEY (GroupID) REFERENCES Group(GroupID)
);

Pros:

  • Just have to maintain one permissions table (Group_Permission).
  • Just need to request information from Group_Permission to know about group permissions.
  • Much smaller than using the user-based approach.

Cons:

  • Generic ItemID where I would not know where it came from without looking for its ID in each item table.
  • No Foreign Keys.
  • ItemID could repeat (I could change VideoID/etcID to UNIQUEIDENTIFIER, but there still a very small chance that it could be the same).
  • It would have group permissions, a smaller possibility of custom user permissions.

3) Create table for Group, User and each category of items (Group_Video, Group_Image, etc):

CREATE TABLE User (
    UserID INT IDENTITY,
    AdminID INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255) NOT NULL,
    ...
);

CREATE TABLE Group (
    GroupID INT IDENTITY,
    AdminID INT NOT NULL,
    Description VARCHAR(255) NOT NULL,
    ...
);

CREATE TABLE Group_User (
    GroupID INT NOT NULL,
    UserID INT NOT NULL,
    CONSTRAINT PK_groupuser PRIMARY KEY (GroupID, UserID),
    CONSTRAINT FK_groupuser_groupid FOREIGN KEY (GroupID) REFERENCES Group(GroupID),
    CONSTRAINT FK_groupuser_userid FOREIGN KEY (UserID) REFERENCES User(UserID)
);

CREATE TABLE Group_Video (
    GroupID INT NOT NULL,
    VideoID INT NOT NULL,
    CONSTRAINT PK_groupvideo PRIMARY KEY (GroupID, VideoID),
    CONSTRAINT FK_groupvideo_groupid FOREIGN KEY (GroupID) REFERENCES Group(GroupID),
    CONSTRAINT FK_groupvideo_videoid FOREIGN KEY (VideoID) REFERENCES Video(VideoID)
);

CREATE TABLE Group_Image (
    GroupID INT NOT NULL,
    ImageID INT NOT NULL,
    ...
);
...

Pros:

  • Each table will have Foreign Keys for its item category(FK_groupvideo_groupid).
  • It's easy to know what item category I'm trying to get.
  • VideoID/ImageID/etc would not repeat.
  • Much smaller than using the user-based or user-video/user-image approach.

Cons:

  • A larger number of tables, because I will have to create a new table for each new item category.
  • More requests for permissions than the previous ones.
  • It would have group permissions, a smaller possibility of custom user permissions.

Is there another option I'm missing?

What approach should I use and why?

Cheers.

  • 1
    I think you are putting the carriage in front of the horse... before you start writing down your tables, you need to clearly describe the data model and the business requirements for the permission module. This is what will determine the table structure, not the other way around, – SQLRaptor Dec 7 at 0:39
  • In addition,you may be overthinking your permission sets. A likely scenario would be that items in more than one table would need to be granted permissions simultaneously. For example, having a quiz that contains ten images and two videos, you obviously want those items granted simultaneously. So, instead of granting permissions to each item each time, set up "permission sets" (multiple ways to do that) and grant users access to permission sets instead. So, permissionSet Q1 grants access to the quiz, images, and videos simultaneously with one added row entered in a user_permissionset structure – Laughing Vergil Dec 7 at 1:04
  • @SQLRaptor Sorry about that, I've edited my question and have added data model and business requirements. – Felipe Macario Dec 7 at 21:36
  • @LaughingVergil I like you approach to this question, but it could get very hard to maintain don't you think? Because if I create a permissionSet to Quiz number 1 that has access to 10 images and 2 videos, and permissionSet to Quiz number 2 that has access to the same images but only 1 video, I would have to create a permissionSet for each possibilities that appear, or did I get it wrong? – Felipe Macario Dec 7 at 21:41
  • @FelipeMacario Please do not cross post across sites - delete one of the questions. – LowlyDBA Dec 7 at 21:49

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.