用户级逻辑

Question

I have a project that I am working on and I've been researching about having User Levels and the recommended & secure ways of doing it. The project will have some more complex stuff like groups, users, pages, chat and I don't want to make some changes now and be required to re-change the logic when I will add the other features. I would like some advice or help on this, I would appreciate it very much.

The way I was thinking was either

1. Have a table named userlevels (for example) with 3 columns ID, Name, Permissions

   1, user
   
   2, Administrator, {"admin": 1}     with json
   

OR

1. Have an additional row with permissions in the normal users table, and have a row for example

   is_admin  [0,1]
   

Is it okay to do it like this ? If you have any other ideas for me I would honestly appreciate it very much. Thanks in Advance!

Answer 1

I'll propose a more normalized solution that should be completely flexible going forward.

1. A table of users:

   UserID (PK) 
   UserName
   

2. A table of permissions

   PermissionId (PK)
   Permission
   

3. A junction table to resolve the many-to-many relationship between users and permissions

   UserID (FK to users)
   PermissionId (FK to permissions)