0

What I want to do is a permission system. there are several permissions for a group like GetGroup,UpdateGroup,DeleteGroup or others and no need to add or delete. I want to :

  1. easily to change the permission of each user for a group.
  2. can get relations of each user by a group_id or permission_id.

So I consider the table like:

CREATE TABLE [Permissions] (
    [Id] int NOT NULL IDENTITY,
    [UserId] int NOT NULL,
    [ClaimType] int NOT NULL,
    CONSTRAINT [PK_Permissions] PRIMARY KEY ([Id])
);

So I can get if a user has permission to a group:

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [Permissions] AS [p]
        WHERE ([p].[UserId] = @__userId_0) AND ([p].[ClaimType] = 0)) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END

But the question is there maybe 20 permission of a group(possible) and a user will have 30 groups so there will be 600 count of permissions for a user? is is too large?

So I want to change the column of ClaimType to [ClaimType] varchar(1000) NOT NULL and I can use 1 permission to get the all claims that a user has of a group. like :GetGroup;UpdateGroup;DeleteGroup. But it may be the better solutions and I am a newbie to ask for a better table to do it.

and the relation between the users is one's permission contains another.

for example user01 is the top user and he has all the permissions and he give some to user02 user03 user005 and then user02 give some permission to user0201 and user03 give some permission to user0301 and user0302 then there will be :

Id UserId ClaimType ClaimId
1 user01 GetGroup;UpdateGroup;DeleteGroup group01,group02,group03,group10,group11
2 user02 getGroup;UpdateGroup;DeleteGroup; group01,group02
3 user03 GetGroup;UpdateGroup;DeleteGroup group03
4 user005 GetGroup;UpdateGroup;DeleteGroup group10,group11
5 user0201 GetGroup;UpdateGroup; group01
6 user0301 GetGroup; group03
7 user0302 UpdateGroup;DeleteGroup; group03

and the relation will be:

                                    user01
                     user02         user03            user005
                     user0201    user0301  user0302
user01-user02 and user03 and user005
user02-user0201
user03-user0301 and user0302

and I can get all the relation of the users from a claimId (groupId).

what do you suggests the table be?

user666
  • 329
  • 1
  • 7
  • 22
  • 2
    Normalize your schema. For example, this can be: users table (userid, user data), groups table (groupid, group data), permissions table (permissionid, permission data), adjacency table (adjacency1id, userid, groupid), another adjacency table (adjacency2id, permissionid, adjacency1id). – Akina Dec 02 '21 at 06:06
  • Read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** – Zohar Peled Dec 02 '21 at 06:27

0 Answers0