MySQL table design practice: Create an e-commerce order table and product review table

MySQL table design practice: Create an e-commerce order table and product review table

In the database of the e-commerce platform, the order table and product review table are two very important tables. This article will introduce how to use MySQL to design and create these two tables, and give code examples.

1. Design and Creation of Order Table
The order table is used to store user purchase information, including order number, user ID, product ID, purchase quantity, order status and other fields.

First, we need to create a table named "order" and use the CREATE TABLE statement:

CREATE TABLE `order` (
  `order_id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `quantity` INT NOT NULL,
  `status` ENUM('待付款', '待发货', '已发货', '已完成') NOT NULL,
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

The table name here is order, because order is a reserved word of MySQL, so you need to use Backticks (``) are escaped.

In the table, order_id is the primary key field and is automatically incremented using AUTO_INCREMENT. user_id and product_id are foreign keys respectively, corresponding to the primary keys of the user table and product table. The quantity field represents the purchase quantity, and the status field represents the order status. The ENUM type is used to limit the value range. create_time and update_time represent the creation time and modification time of the order respectively.

2. Design and creation of product review table
The product review table is used to store user evaluation information for products, including review ID, user ID, product ID, review content, rating and other fields.

Similarly, we need to create a table named "comment" and use the CREATE TABLE statement:

CREATE TABLE `comment` (
  `comment_id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `content` VARCHAR(255) NOT NULL,
  `rating` INT NOT NULL,
  `create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `update_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

The comment_id in the table is the primary key field and is incremented using the AUTO_INCREMENT setting. user_id and product_id are foreign keys respectively, corresponding to the primary keys of the user table and product table. The content field is used to store the comment content, using the VARCHAR type to set the maximum length to 255 characters. The rating field represents the rating, which can be represented by an integer. create_time and update_time represent the creation time and modification time of the comment respectively.

3. Relationship between tables
In the order table and product review table, the user_id and product_id fields correspond to the primary keys of the user table and product table respectively. This can establish relationships between tables and facilitate data query and analysis.

For example, if you need to find the order information of a certain user, you can use the following query statement:

SELECT * FROM `order` WHERE user_id = 123;

Similarly, if you need to find the review information of a certain product, you can use the following query statement:

SELECT * FROM `comment` WHERE product_id = 456;

Through the relationship between tables, data query, statistics and analysis can be easily carried out.

Summary:
This article introduces how to use MySQL to design and create e-commerce order tables and product review tables. Through reasonable table design and establishing relationships between tables, order and comment information on e-commerce platforms can be easily stored, queried, and analyzed. I hope it will be helpful to readers in their table design and database development in actual projects.

The above is the detailed content of MySQL table design practice: Create an e-commerce order table and product review table. For more information, please follow other related articles on the PHP Chinese website!