如何处理MySQL中的订阅订单和非订阅订单?

Question

mysql会员订阅数据表的设计应该如何设计？产品有订阅商品和非订阅的，每次都只能购买一个。
订阅有1个月 3个月的 每次到期自动扣费。如果在一个月类购买了几个订阅商品 则扣费按照最新的一个 然后延长到期时间。其实是不是每次订阅都不需要生成新订单的

翻阅了其他资料都找不到很好的设计

Answer 1

CREATE TABLE `users` (
    `user_id` INT(11) PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(100) NOT NULL,
    `email` VARCHAR(255) UNIQUE NOT NULL,
    -- 其他用户相关信息...
);

CREATE TABLE `products` (
    `product_id` INT(11) PRIMARY KEY AUTO_INCREMENT,
    `product_name` VARCHAR(100) NOT NULL,
    `is_subscription` BOOLEAN NOT NULL DEFAULT FALSE, -- 标记是否为订阅商品
    `subscription_type` ENUM('monthly', 'quarterly') DEFAULT NULL, -- 订阅类型（1个月或3个月）
    `price` DECIMAL(10, 2) NOT NULL,
    -- 其他商品相关信息...
);

CREATE TABLE `orders` (
    `order_id` INT(11) PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT(11),
    `product_id` INT(11),
    `order_date` DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(user_id),
    FOREIGN KEY (`product_id`) REFERENCES `products`(product_id),
    -- 其他订单相关信息，如交易状态、支付信息等
);

CREATE TABLE `subscriptions` (
    `subscription_id` INT(11) PRIMARY KEY AUTO_INCREMENT,
    `user_id` INT(11),
    `product_id` INT(11),
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `next_renewal_date` DATE NOT NULL,
    `latest_order_id` INT(11) DEFAULT NULL, -- 可选，记录最近一次产生订单的ID
    FOREIGN KEY (`user_id`) REFERENCES `users`(user_id),
    FOREIGN KEY (`product_id`) REFERENCES `products`(product_id),
    FOREIGN KEY (`latest_order_id`) REFERENCES `orders`(order_id) ON DELETE SET NULL
);

配合后台逻辑实现自动扣费，并在扣费成功后更新订阅表的end_date和next_renewal_date字段