MySQL table design practice: Create a coupon table and usage record table

MySQL table design practice: Create a coupon table and usage record table

In many business scenarios, coupons are a common promotional tool. In order to effectively manage and track the usage of coupons, we need to design a coupon table and a usage record table. This article walks you through how to create these two tables and provides corresponding code examples.

Coupon table design

First, we need to create a coupon table to store all available coupon information. Here is an example of a basic coupon table design:

CREATE TABLE `coupon` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(50) NOT NULL,
  `discount` DECIMAL(10,2) NOT NULL,
  `valid_from` DATETIME NOT NULL,
  `valid_to` DATETIME NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_UNIQUE` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

In the above design, we have used the following fields:

• id: Coupon’s Unique identifier, using an auto-increasing integer as the primary key.
• code: The code of the coupon, usually a string of characters or numbers.
• discount: The discount amount or discount ratio of the coupon.
• valid_from: The coupon’s validity start date and time.
• valid_to: The coupon’s validity end date and time.
• created_at: The creation time of the coupon, automatically generated using MySQL's CURRENT_TIMESTAMP function.

Usage record table design

Next, we need to create a usage record table to record the usage of each coupon. The following is a basic usage record table design example:

CREATE TABLE `coupon_usage` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `coupon_id` INT(11) NOT NULL,
  `user_id` INT(11) NOT NULL,
  `used_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `coupon_id_idx` (`coupon_id`),
  KEY `user_id_idx` (`user_id`),
  CONSTRAINT `coupon_id_fk` FOREIGN KEY (`coupon_id`) REFERENCES `coupon` (`id`),
  CONSTRAINT `user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

In the above design, we used the following fields:

• id: usage record Unique identifier, using an auto-increasing integer as the primary key.
• coupon_id: The ID of the coupon, corresponding to the id field in the coupon table.
• user_id: The ID of the user who used the coupon.
• used_at: The usage time of the coupon.

In order to ensure the consistency and integrity of the data, we also created two foreign key constraints, connecting the coupon_id field with the coupon table The ##id field is associated, and the user_id field is associated with the id field in the user table. Doing this ensures that every usage record is valid.

Through the above table design, we can easily manage and track the usage of coupons. You can expand and optimize the table according to actual needs to adapt to different business scenarios.

Summary

In this article, we show readers the design ideas of a complete coupon management system by creating a coupon table and using the MySQL table design practice of the record table. Through reasonable table structure design and foreign key constraints, we can effectively manage and track the usage of coupons. I hope this article will be helpful to readers in actual development.

The above is the detailed content of MySQL table design practice: Create a coupon table and usage record table. For more information, please follow other related articles on the PHP Chinese website!