User points table design guide for grocery shopping system in MySQL

User Points Table Design Guide for Food Purchasing System in MySQL

Introduction:
User points are a very important part of the food purchase system, which can reflect the user loyalty and activity in the system. Designing a suitable user points table can facilitate us to add, subtract, query and count points. This article will introduce in detail how to design a user points table, including table structure design, field meanings and code examples.

1. Table structure design
To create a user points table in MySQL, we can use the following table structure design:

CREATE TABLE `user_points` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `point` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Here we define 5 fields:

1. id: auto-incremented primary key, used to uniquely identify each points record.
2. user_id: User ID, used to associate user information in the user table.
3. point: The number of user points, used to indicate the number of points of the user.
4. created_at: Record creation time, used to record the generation time of points.
5. updated_at: Record update time, used to record the modification time of points.

2. Field meaning description

1. id: As the primary key of the table, it is used to uniquely identify each points record.
2. user_id: The user ID in the associated user table, used to determine which user's points record.
3. point: Indicates the user’s number of points, which can be a positive or negative number.
4. created_at: Record the generation time of points, automatically generated using MySQL's CURRENT_TIMESTAMP function.
5. updated_at: Record the modification time of points, automatically generated using MySQL's CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP function.

3. Code example

1. Insert user points record

   INSERT INTO `user_points` (`user_id`, `point`) VALUES (1, 10);

   This code will insert a user ID into the user points table is 1 and the number of points is 10 records.

2. Increase user points

   UPDATE `user_points` SET `point` = `point` + 5 WHERE `user_id` = 1;

   This code will increase the number of points for user ID 1 by 5.

3. Reduce user points

   UPDATE `user_points` SET `point` = `point` - 5 WHERE `user_id` = 1;

   This code will reduce the number of points for user ID 1 by 5.

4. Query user points

   SELECT `point` FROM `user_points` WHERE `user_id` = 1;

   This code will query the number of points for user ID 1.

5. Query user points ranking

   SELECT `user_id`, `point`, (
    SELECT COUNT(*) + 1
    FROM `user_points` AS `up2`
    WHERE `up2`.`point` > `up1`.`point`
   ) AS `ranking`
   FROM `user_points` AS `up1`
   ORDER BY `point` DESC;

   This code will query the point rankings of all users in the user points table.

Conclusion:
A well-designed user points table can facilitate the increase, decrease, query and statistics of user points. Using basic SQL statements in MySQL, we can easily operate the user points table. I hope the user points table design guide provided in this article will be helpful to your grocery shopping system.

The above is the detailed content of User points table design guide for grocery shopping system in MySQL. For more information, please follow other related articles on the PHP Chinese website!