How to create a user role permission table for the grocery shopping system in MySQL

How to create the user role permission table of the vegetable shopping system in MySQL

The vegetable shopping system is a common online shopping platform. In order to ensure the security and safety of the system The privacy of data requires the management of user role permissions in the system. In the MySQL database, user permissions can be managed by creating a user role permissions table.

The user role permission table mainly includes role table, permission table and user role association table. The role table is used to store role information in the system, the permission table is used to store permission information in the system, and the user role association table is used to establish the relationship between users and roles. A specific example of creating a user role permission table is given below.

First, create the role table (role):

CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Secondly, create the permission table (permission):

CREATE TABLE `permission` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Finally, create the user role association table (user_role):

CREATE TABLE `user_role` (
  `role_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`role_id`,`user_id`),
  CONSTRAINT `fk_user_role_role` FOREIGN KEY (`role_id`) 
    REFERENCES `role` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_role_user` FOREIGN KEY (`user_id`) 
    REFERENCES `user` (`id`) ON DELETE CASCADE
);

In the above code, the user_role table uses a joint primary key (role_id, user_id) to ensure the uniqueness of the relationship between users and roles. At the same time, through foreign key constraints, the consistency of the relationship between users and roles can be ensured.

In actual applications, more fields and constraints can be added according to specific business needs. For example, you can add a create_time field to the role table to record the creation time of the role; in the user role association table, you can add a status field to record the status of the relationship between the user and the role (such as deactivation, activation, etc. ).

To sum up, by creating a user role permission table, the user permissions of the food shopping system can be managed. In this way, user permissions can be easily controlled and managed, improving system security and user experience.

The above is the detailed content of How to create a user role permission table for the grocery shopping system in MySQL. For more information, please follow other related articles on the PHP Chinese website!