Background management user Rights database design

From Prism Academy-online IT education www.prismcollege.com

1. Create a basic table

Create user, role, and module three tables, each row in user corresponds to one person's information, role each line holds a character information, including the role ID and the corresponding name,module each row to save a module information, including the module ID and the corresponding name.

Create three tables of SQL statements (MySQL environment) as follows:

1) Create a user table with the following table structure:

2) Create a role table with the following table structure:

3) Create a module table with the following table structure:

CREATE TABLE ' Of_user ' (
' user_id ' varchar (100) ',
' USERNAME ' varchar (255),
' PASSWORD ' varchar (255),
' NAME ' varchar (255),
' role_id ' varchar (100),
' Number ' varchar (100),
PRIMARY KEY (' user_id ')
) Engine=innodb DEFAULT Charset=utf8;

CREATE TABLE ' Of_role ' (
' role_id ' varchar (50),
' role_name ' varchar (100),
PRIMARY KEY (' role_id ')
) Engine=innodb DEFAULT Charset=utf8;

CREATE TABLE ' Of_menu ' (
' menu_id ' int (one) is not NULL,
' Menu_name ' varchar (254),
' Menu_url ' varchar (254),
' parent_id ' varchar (20),
' Menu_order ' varchar (80),
' Menu_icon ' varchar (30),
PRIMARY KEY (' menu_id ')
) Engine=innodb DEFAULT Charset=utf8;

CREATE TABLE ' Of_user_group ' (
' ID ' varchar (50),
' user_id ' varchar (50),
' group_id ' varchar (50),
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8;

2. Inserting initial data

1) Define the module, assuming the following module a. Query report B. Account management C. Information release D. List of registered users E. Recruitment information list several modules, insert modules into the module table.

Insert the following module contents as follows:

2) Define roles, and initially define the following roles a. Super Administrator B. Administrator C. Information publisher D. Job seeker, etc., insert post role content as follows:

3) Define the user, the initial user is as follows, Liubei,zhugeliang,zhangfei,lvbu

After inserting the user content as follows

3. Create an association table

1) Create an association table for user and role User_role, one user for one or more roles, one role can correspond to multiple user, and the Liubei role is administrator. Zhugeliang for admin and Distributor,zhangfei for Distributor,lvbu for jobseeker. There is a one-to-many relationship between user Zhugeliang and role, and a one-to-many relationship between roles distributor and user.

The User_role table structure after creation is as follows:

The following content preserves the mapping between users and roles:

2) Create association mapping table between role and module Role_module,administrator can view Journal_sheet module, admin can view all modules except Journal_sheet: Info_ Distribute,account_manage,user_list,job_list,distributor can view the Info_distribute module, jobseeker can view the Job_list module.

The Role_module table structure after creation is as follows:

The contents are as follows, preserving the mapping relationship between role and module

CREATE TABLE ' Of_user_role ' (
' user_id ' int (50),
' role_id ' int (50),
PRIMARY KEY (' user_id ')
) Engine=innodb DEFAULT Charset=urf-8;

CREATE TABLE ' Of_role_menu ' (
' role_id ' int (50),
' menu_id ' int (50)
) Engine=innodb DEFAULT Charset=utf8;

At this point, through the database to complete the user rights design.

Learn more Welcome Add QQ Group Exchange:3686148499368614849368614849368614849

Background management user Rights database design