How to design an efficient MySQL mall table structure?

How to design an efficient MySQL mall table structure?

MySQL is currently one of the most popular relational database management systems and is widely used in various shopping mall platforms. Properly designing the database table structure can improve the performance and scalability of the system. This article will introduce how to design an efficient MySQL mall table structure, and attach specific code examples.

1. Product table

In the mall system, products are one of the core data. In order to manage and operate products, we first need to create a product table. The following is a code example for a simple product table:

CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2 ) NOT NULL,
description TEXT,
stock INT NOT NULL,
category_id INT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

This table contains basic information about the product, such as product name, price, description, inventory, etc. At the same time, it also includes the product category information (category_id), as well as the creation time and update time.

2. Classification table

In order to classify and filter products, we need to create a classification table. The following is a code example for a simple classification table:

CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
parent_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

The classification table contains the name of the classification and the ID of the parent classification. Multi-level classification hierarchies can be implemented using parent IDs.

3. Order table

Another important data in the mall system is order data. In order to record the user's purchases, we need to create an order table. The following is a code example for a simple order table:

CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
total_amount DECIMAL(10, 2) NOT NULL ,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

The order table contains the basic information of the order, such as user ID (user_id) and total order amount ( total_amount).

4. Order product table

In order to record the product information in the order, it is necessary to design an order product table. The following is a code example for a simple order product table:

CREATE TABLE order_products (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Order item table Contains information such as order ID (order_id), product ID (product_id), quantity (quantity), and price (price).

5. User table

The mall system also needs a user table to manage user information. The following is a code example for a simple user table:

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100) NOT NULL,
password VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

The user table contains the basic information of the user Information, such as username, password, email, etc.

The above is a simple but efficient MySQL mall table structure design example. By properly designing the table structure and performing operations such as indexing and association, the performance and scalability of the system can be improved. Of course, in practical applications, it also needs to be flexibly adjusted and optimized according to specific needs.

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