MySQL数据库基础练习系列45、优惠券发放系统

-- 用户表
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    gender ENUM('男', '女') NOT NULL,
    phone VARCHAR(20)
);


-- 优惠券类型表
CREATE TABLE CouponTypes (
    coupon_type_id INT AUTO_INCREMENT PRIMARY KEY,
    type_name VARCHAR(50) NOT NULL,
    discount_rate DECIMAL(4, 2) NOT NULL,  -- 折扣率
    max_usage_per_user INT DEFAULT NULL    -- 每个用户最大使用次数
);


-- 优惠券表
CREATE TABLE Coupons (
    coupon_id INT AUTO_INCREMENT PRIMARY KEY,
    coupon_type_id INT NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,      -- 优惠券码
    start_date DATE NOT NULL,             -- 开始日期
    end_date DATE NOT NULL,               -- 结束日期
    usage_count INT DEFAULT 0,             -- 已使用次数
    status ENUM('active', 'expired', 'used_up') NOT NULL DEFAULT 'active',  -- 优惠券状态
    FOREIGN KEY (coupon_type_id) REFERENCES CouponTypes(coupon_type_id)
);


-- 用户领取优惠券记录表
CREATE TABLE UserCoupons (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    coupon_id INT NOT NULL,
    receive_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 领取时间
    is_used ENUM('no', 'yes') NOT NULL DEFAULT 'no',  -- 是否已使用
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (coupon_id) REFERENCES Coupons(coupon_id)
);


-- 优惠券使用记录表（可选，用于记录每次使用的详情）
CREATE TABLE CouponUsageLogs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    record_id INT NOT NULL,
    usage_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 使用时间
    order_id VARCHAR(50),                           -- 订单号（可选）
    amount DECIMAL(10, 2),                          -- 使用金额
    FOREIGN KEY (record_id) REFERENCES UserCoupons(record_id)
);

INSERT INTO Users (username, password, name, email, gender, phone) VALUES
('乔峰', '123', '乔峰', 'qiaofeng@email.com', '男', '13800138001'),
('小龙女', '123', '小龙女', 'xiaolongnu@email.com', '女', '13900139002'),
('令狐冲', '123', '令狐冲', 'linghuchong@email.com', '男', '13700137003');
INSERT INTO CouponTypes (type_name, discount_rate, max_usage_per_user) VALUES
('满100减20', 20.00, 3),
('全场9折', 10.00, 5),
('新用户专享', 50.00, 1);
INSERT INTO Coupons (coupon_type_id, code, start_date, end_date, status) VALUES
(1, 'CT10020-A', '2023-01-01', '2023-12-31', 'active'),
(1, 'CT10020-B', '2023-01-01', '2023-12-31', 'active'),
(2, 'CT90-A', '2023-01-01', '2023-12-31', 'active');
INSERT INTO UserCoupons (user_id, coupon_id) VALUES
(1, 1), -- 乔峰领取了满100减20优惠券A
(2, 2), -- 小龙女领取了全场9折优惠券A
(3, 1), -- 令狐冲也领取了满100减20优惠券A
(1, 3); -- 乔峰再领取了新用户专享优惠券
INSERT INTO CouponUsageLogs (record_id, order_id, amount) VALUES
(1, 'ORDER123', 80.00), -- 乔峰使用了领取的满100减20优惠券A，订单金额为80元
(3, 'ORDER456', 150.00); -- 令狐冲使用了领取的满100减20优惠券A，订单金额为150元