AlexMp AlexMp - 1 year ago 50
SQL Question

MYSQL PDO: Multiples users with the same rank. How to make the db structure?

First of all I'll tell you what I want to do.

First make a system role. That is (User, mod, admin, etc).

And add an option to add roles and these are stored in a different user table.

In better words. I need to know how to structure the database (tables) to match a role.

That is:

  • All new must have the user role.

  • If I am an administrator, I can add
    new roles and edit the user.

  • A member may have a role.

  • Roles can have many users.

    Divided into two tables:

    Site_table = stores ranges and site configurations.

    User_table = Store registration information and user input.

Answer Source

I'd go with 4 tables (only important information for this purpose added to tables):

  -- id
  -- role_id

  -- id
  -- name

  -- id
  -- name
  -- value

  -- privilege_id
  -- role_id
  • Relationship users <-> roles is 1:1 as one user should be related to one role.
  • Relationship roles <-> privileges is 1:n as one role should consist of lots of different privileges (e.g. writing articles, moderating new comments, etc.).