How to design the order table structure of the mall in MySQL?

How to design the order table structure of the mall in MySQL?

In a mall system, order is a core data module. The design of the order table needs to take into account the basic information of the order, product information, user information and other aspects. This article will introduce how to design the order table structure of the mall in MySQL and provide corresponding code examples.

1. Basic information of the order table

The basic information of the order table includes order number, order creation time, order status, etc. The following is a simple order table structure example:

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_number` varchar(20) NOT NULL,
  `create_time` datetime NOT NULL,
  `status` int(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_number` (`order_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The order table in this example contains four fields: id, order_number, create_time, status. Among them, id is the unique identifier of the order, order_number is the order number, create_time is the order creation time, and status is the order status (for example, 0 means pending payment, 1 means paid, -1 means canceled, etc.).

2. The relationship between the order table and the product table

There is a many-to-many relationship between the order table and the product table, that is, an order can contain multiple products, and a product can also be Multiple order purchases. In order to realize this relationship, an order product table can be established to record the relationship between orders and products. The following is an example of the relationship between the order table and the product table:

CREATE TABLE `order_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `order_item_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`) ON DELETE CASCADE,
  CONSTRAINT `order_item_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In the order product table, there are four fields: id, order_id, product_id, quantity. Among them, id is the unique identifier of the order product table, order_id is the id of the order, product_id is the id of the product, and quantity is the purchase quantity. At the same time, in order to ensure data consistency, foreign key constraints need to be defined to ensure that the order_id and product_id fields of the order product table are associated with the id fields of the order table and product table respectively.

3. The relationship between the order table and the user table

In the mall system, one user can place multiple orders, but one order only belongs to one user. Therefore, the order table and the user table have a one-to-many relationship. In order to achieve this relationship, the order table needs to contain a user id field. The following is an example of the relationship between the order table and the user table:

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_number` varchar(20) NOT NULL,
  `create_time` datetime NOT NULL,
  `status` int(1) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_number` (`order_number`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In the order table, a new user_id field is added, which is used to associate to the id field of the user table. At the same time, foreign key constraints also need to be defined to ensure that the user_id field of the order table is associated with the id field of the user table.

4. Summary

Through the above examples, we can design a basic mall order table structure. The order table contains the basic information of the order, the order product table records the relationship between the order and the product, and the order table has an associated relationship with the user table. According to actual needs, it can also be expanded and optimized on the above basis. Through reasonable database table design, functions such as mall order management, query and statistics can be realized.

Note: The above sample code is for reference only, and the specific database table design should be adjusted according to actual business needs.

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