How to design the mall’s comments table structure in MySQL?
When designing the mall's comment table structure, we need to consider factors such as the commentator, the object of the comment, and the content of the comment. Below, I will introduce you in detail how to design the mall's review table structure in MySQL and give you specific code examples.
First, we need to create a table named comments
to store all comment information. Below is a code example to create a comments
table:
CREATE TABLE comments ( comment_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, product_id INT NOT NULL, comment_text TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (product_id) REFERENCES products(product_id) );
In the above code, we create a comments
table with the following fields:
comment_id
: The unique identifier of the comment, using the INT
type, and set as the primary key. user_id
: The user ID of the comment author, using the INT
type, and set to non-empty. product_id
: The product ID of the review object, using the INT
type, and set to non-empty. comment_text
: The content of the comment, using the TEXT
type. created_at
: The creation time of the comment, use the DATETIME
type, and set the default value to the current time. Next, we need to create two foreign key constraints to associate the user_id
field and the product_id
field with corresponding fields in other tables. This ensures that no redundant data appears when related records are deleted. In the above code, we use the FOREIGN KEY
statement to create two foreign key constraints, where the user_id
field is the same as the user_id# in the
users table ##Field association, the
product_id field is associated with the
product_id field in the
products table.
users table and
products table to save user and product information. Here, we only give sample code, you can modify it according to the actual situation:
CREATE TABLE users ( user_id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, -- 其他用户信息字段 ); CREATE TABLE products ( product_id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, price DECIMAL(10, 2) NOT NULL, -- 其他商品信息字段 );In the above code, we created the
users table and the
products table , and added corresponding fields to save user and product information.
The above is the detailed content of How to design the mall's review table structure in MySQL?. For more information, please follow other related articles on the PHP Chinese website!