Set roles for users stored in Database

Question

How to structure in the database a structure of user roles (ordinary users, entrepreneur, site_amdin), each user will have different functionalities, different powers in the application that will use this database.

User table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created DATETIME,
    modified DATETIME
);

OBS: Mysql Database. *It’s following Cakephp naming conventions.

Answer 1

I would do it in a very simple way with a user type table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    user_type_id INT,
    created DATETIME,
    modified DATETIME
);

CREATE TABLE user_type (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created DATETIME,
    modified DATETIME
);

Answer 2

I would follow the same logic of Jorge, but with a complement:

Would do with module permission

CREATE TABLE modulos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    modulo VARCHAR(255) NOT NULL,
);

CREATE TABLE user_permissao (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_user INT,    
    id_modulo INT,
    permissao VARCHAR(1) NOT NULL,
);