How to structure a user roles structure (common users, businessperson, site_amdin) in the database, each user will have different functions, 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 is following CakePHP naming conventions.
I would do this quite simply 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
);
I would follow the same logic as Jorge, but with a complement:
I 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,
);