How to design an scalable MySQL table structure to implement the grouping function?

How to design an extensible MySQL table structure to realize the grouping function?

Group buying is a popular shopping model that can attract more users to participate in purchases and increase merchants’ sales. In order to implement the group-buying function, we need to design an scalable MySQL table structure that can store information about users, group-buying activities, and group-buying orders. This article will introduce in detail how to design this database schema, with sample code.

Step one: Create user table
The user table is used to store basic information of users, including user ID, name, phone number, etc. We can use the following MySQL statement to create a user table:

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  `phone` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Step 2: Create a group activity table
The group activity table is used to store information about group activities, including activity ID, activity name, Start time, end time, etc. We can use the following MySQL statement to create a group buying activity table:

CREATE TABLE `group_activity` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `start_time` DATETIME NOT NULL,
  `end_time` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Step 3: Create a group buying order table
The group buying order table is used to store the order information of users participating in group buying, including order IDs , user ID, activity ID, group status, etc. We can use the following MySQL statement to create the group buying order table:

CREATE TABLE `group_order` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `activity_id` INT(11) NOT NULL,
  `status` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  FOREIGN KEY (`activity_id`) REFERENCES `group_activity` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Sample code:
The following are some sample codes to show how to use the designed table structure to implement the group buying function.

1. Insert user information:

INSERT INTO `user` (`name`, `phone`) VALUES ('张三', '123456789');

2. Insert group activity information:

INSERT INTO `group_activity` (`name`, `start_time`, `end_time`) VALUES ('618拼团', '2022-06-18 00:00:00', '2022-06-20 00:00:00');

3. Insert group order information :

INSERT INTO `group_order` (`user_id`, `activity_id`, `status`) VALUES (1, 1, 0);

4. Query all order information for a specific event:

SELECT * FROM `group_order` WHERE `activity_id` = 1;

5. Query all group order information for a specific user:

SELECT * FROM `group_order` WHERE `user_id` = 1;

Through the above steps, we designed an extensible MySQL table structure to implement the grouping function, and provided some sample code to demonstrate how to use this table structure. You can expand the table according to actual needs, such as adding product tables, team tables, etc. I hope this article can help you understand how to design an extensible MySQL table structure to implement the grouping function.

The above is the detailed content of How to design an scalable MySQL table structure to implement the grouping function?. For more information, please follow other related articles on the PHP Chinese website!