Home / CMS
Today we are going to make a user system, which will be used for login, user management, role permission, page access control etc.
1 - team
CREATE TABLE team (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_up int NOT NULL DEFAULT 0, -- parent team id
name varchar(99) NOT NULL DEFAULT '',
info varchar(255) NOT NULL DEFAULT '',
UNIQUE KEY team_uk_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Other name for this table can be company, organisation, unit, faculty etc.
2 - user
This table is used to save all users for login purpose. It can be mixed of staff and customers. Common fields will be:
CREATE TABLE user (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(99) NOT NULL DEFAULT '',
email varchar(99) NOT NULL DEFAULT '',
created timestamp NOT NULL DEFAULT now(),
closed date NOT NULL DEFAULT '1970-01-01',
team_id int NOT NULL DEFAULT 0,
sid int NOT NULL DEFAULT 0,
error smallint NOT NULL DEFAULT 0,
pass varchar(255) NOT NULL DEFAULT '',
UNIQUE KEY user_uk_email (email),
CONSTRAINT user_fk_team FOREIGN KEY (team_id) REFERENCES team (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Other common fields might be: gender, dob, address, suburb, state, postcode, etc
3 - role
CREATE TABLE role (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(99) NOT NULL DEFAULT '',
info varchar(255) NOT NULL DEFAULT '',
UNIQUE KEY role_uk_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
4 - task
CREATE TABLE task (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(99) NOT NULL DEFAULT '',
info varchar(255) NOT NULL DEFAULT '',
UNIQUE KEY task_uk_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Other name for this table can be permission, access etc
5 - user_role
CREATE TABLE user_role (
user_id int NOT NULL DEFAULT 0,
role_id int NOT NULL DEFAULT 0,
PRIMARY KEY (user_id,role_id),
CONSTRAINT user_role_fk_role FOREIGN KEY (role_id) REFERENCES role (id),
CONSTRAINT user_role_fk_user FOREIGN KEY (user_id) REFERENCES user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
6 - role_task
CREATE TABLE role_task (
role_id int NOT NULL DEFAULT 0,
task_id int NOT NULL DEFAULT 0,
PRIMARY KEY (role_id,task_id),
CONSTRAINT role_task_fk_role FOREIGN KEY (role_id) REFERENCES role (id),
CONSTRAINT role_task_fk_task FOREIGN KEY (task_id) REFERENCES task (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Also try to go through all business logic, and check if the SQL query performance works well, and make adjustment if needed.
Back «
Topnew CSS v 2023 JS related
Comments
Leave a commentEdit comment