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

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

With the rapid development of e-commerce, coupons have become one of the important marketing methods to attract users. In a shopping mall system, it is very important to properly design the structure of the coupon table. This article will introduce how to design the coupon table structure of the mall in MySQL and provide specific code examples.

1. Basic attributes of mall coupons
   First of all, we need to clarify the basic attributes of mall coupons. Generally speaking, a coupon includes the following attributes:

• Coupon ID: Each coupon should have a unique identifier to facilitate operation and query;
• Coupon name: used to briefly describe the name of the coupon;
• Coupon type: A variety of different coupon types can be defined according to actual needs, such as full discount coupons, discount coupons, free coupons, etc. Postage coupons, etc.;
• Coupon face value: indicates the specific discount or amount of the coupon;
• Minimum consumption amount: Some coupons may require a minimum consumption amount before they can be used;
• Validity start time and end time: indicate the validity range of the coupon;
• Quantity issued and quantity received: record the total number of coupons issued and the number that have been claimed by users.

In MySQL, you can use the following code example to create a table named coupons to store the coupon information of the mall:

CREATE TABLE `coupons` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name ` VARCHAR(100) NOT NULL,
  `type` ENUM('full_reduction', 'discount', 'free_shipping') NOT NULL,
  `value` DECIMAL(10,2) NOT NULL,
  `min_amount` DECIMAL(10,2) DEFAULT 0,
  `start_time` DATETIME NOT NULL,
  `end_time` DATETIME NOT NULL,
  `total_count` INT(11) NOT NULL,
  `claimed_count` INT(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);

2. Mall Association between coupons and users
   In addition to basic attributes, mall coupons also need to be associated with users to facilitate recording of user coupon collection and usage. You can use the following code example to create a table named user_coupons to store information related to users and coupons:

CREATE TABLE `user_coupons` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `coupon_id` INT(11) NOT NULL,
  `claimed_time` DATETIME NOT NULL,
  `used_time` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX (`user_id`),
  INDEX (`coupon_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`)
);

In this table, user_id and coupon_id refer to the unique identifiers in the users table and coupons table respectively, ensuring the integrity of the data through foreign key association.

Through this table, we can easily query the coupons that a user has received and the coupons that have been used.

3. Use scenarios of mall coupons
   There are many usage scenarios of mall coupons, which may be applicable to multiple products, categories or orders at the same time. In order to conveniently record the usage of coupons, you can create a table named coupon_scenes to store the scene information for which the coupon is applicable. Here is one possible design:

CREATE TABLE `coupon_scenes` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `coupon_id` INT(11) NOT NULL,
  `product_id` INT(11) DEFAULT NULL,
  `category_id` INT(11) DEFAULT NULL,
  `order_id` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`coupon_id`) REFERENCES `coupons` (`id`),
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`),
  FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`)
);

In this table, coupon_id references a unique identifier from the coupons table, product_id, category_id and order_id refer to the products table, categories table and orders table respectively The unique identifier achieves data integrity and query convenience through foreign key association.

Through this table, we can record the usage of coupons in different scenarios. For example, a coupon is only applicable to a specific product or a specific order.

To sum up, the MySQL table structure design of a complete mall coupon system should include a coupon basic information table, a user and coupon association table and a coupon applicable scenario table. In the actual development process, the table structure can be appropriately adjusted and expanded according to actual needs.

I hope this article will be helpful to you in designing the coupon table structure of the mall in MySQL. If you have other questions, please feel free to ask.

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