How to design the mall's user collection table structure in MySQL?

How to design the user collection table structure of the mall in MySQL?

When designing a mall database, user collection is one of the important functions. Users can add products they are interested in to their favorites for easy viewing or purchase later. This article will introduce how to design the user collection table structure of the mall in MySQL and provide specific code examples.

1. Requirements Analysis
Before designing the table structure, we first need to analyze the needs of user collections. Specifically, we need to consider the following aspects:

1. User ID: Each user has a unique ID identification.
2. Product ID: Each product has a unique ID identification.
3. Add time: The time when the user added the product to the favorites.
4. Status: The items in the favorites may be canceled by the user, so a field is needed to indicate the current status.

2. Table structure design
Based on the above requirements, we can design the following user favorite table structure:

CREATE TABLE user_favorite (
id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
user_id int(11) NOT NULL COMMENT 'User ID',
product_id int(11) NOT NULL COMMENT 'Product ID',
add_time datetime NOT NULL COMMENT 'Add time',
status tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Status, 1 means valid, 0 means invalid',
PRIMARY KEY (id),
KEY user_id (user_id),
KEY product_id (product_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='User Favorites Table';

Explain the meaning of each field:

1. id: primary key ID, use auto-incrementing primary key to ensure uniqueness.
2. user_id: User ID, associated with the ID in the user table.
3. product_id: Product ID, associated with the ID in the product table.
4. add_time: Add time, use datetime type to store specific time.
5. status: Status, used to indicate the current collection status. The default is 1, which means valid, and 0, which means invalid.

3. Code example

1. Create user favorite table:

CREATE TABLE user_favorite (
id int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
user_id int(11) NOT NULL COMMENT 'User ID',
product_id int(11) NOT NULL COMMENT 'Product ID',
add_time datetime NOT NULL COMMENT 'Add time',
status tinyint(1) NOT NULL DEFAULT ' 1' COMMENT 'status, 1 means valid, 0 means invalid',
PRIMARY KEY (id),
KEY user_id (user_id) ,
KEY product_id (product_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='User Favorites Table';

2. Add user favorite record:

INSERT INTO user_favorite (user_id, product_id, add_time)
VALUES (1, 1001, '2022-01-01 10:00:00');

3. Query user's collection records:

SELECT product_id, add_time FROM user_favorite
WHERE user_id = 1 AND status = 1;

4. Cancel favorites of a product:

UPDATE user_favorite SET status = 0
WHERE user_id = 1 AND product_id = 1001;

The above code example shows how to create a user collection table, add collection records, query the user's collection records and cancel collection of a product.

Summary:
When designing the user collection table structure, you need to consider fields such as user ID, product ID, adding time and status. By properly designing the table structure and using indexes, the query efficiency of the database can be improved. At the same time, in actual use, the table can be optimized and expanded according to business needs.

Note: The above sample code is for reference only, and the specific implementation should be adjusted according to the actual situation.

The above is the detailed content of How to design the mall's user collection table structure in MySQL?. For more information, please follow other related articles on the PHP Chinese website!