507 lines
20 KiB
Markdown
507 lines
20 KiB
Markdown
# 📋 方案A - 数据库设计
|
||
|
||
**设计日期**: 2026-01-23
|
||
**数据库**: peixue (MySQL)
|
||
|
||
---
|
||
|
||
## 📊 表结构设计
|
||
|
||
### 1. 订单表扩展 (order)
|
||
|
||
需要添加以下字段以支持多种支付方式：
|
||
|
||
```sql
|
||
-- 支付方式相关字段
|
||
ALTER TABLE `order` ADD COLUMN `payment_method` VARCHAR(50) COMMENT '支付方式：wallet,timecard,package,wechat,mixed' AFTER `pay_status`;
|
||
ALTER TABLE `order` ADD COLUMN `wallet_amount` DECIMAL(10,2) DEFAULT 0.00 COMMENT '钱包支付金额';
|
||
ALTER TABLE `order` ADD COLUMN `timecard_id` BIGINT COMMENT '使用的次卡ID';
|
||
ALTER TABLE `order` ADD COLUMN `package_id` BIGINT COMMENT '使用的套餐ID';
|
||
ALTER TABLE `order` ADD COLUMN `coupon_id` BIGINT COMMENT '使用的优惠券ID';
|
||
ALTER TABLE `order` ADD COLUMN `coupon_discount` DECIMAL(10,2) DEFAULT 0.00 COMMENT '优惠券折扣金额';
|
||
ALTER TABLE `order` ADD COLUMN `service_address_id` BIGINT COMMENT '服务地址ID';
|
||
|
||
-- 金额相关字段
|
||
ALTER TABLE `order` ADD COLUMN `original_amount` DECIMAL(10,2) COMMENT '原价' AFTER `pay_amount`;
|
||
ALTER TABLE `order` ADD COLUMN `discount_amount` DECIMAL(10,2) DEFAULT 0.00 COMMENT '总折扣金额';
|
||
|
||
-- 添加索引
|
||
ALTER TABLE `order` ADD INDEX `idx_timecard_id` (`timecard_id`);
|
||
ALTER TABLE `order` ADD INDEX `idx_package_id` (`package_id`);
|
||
ALTER TABLE `order` ADD INDEX `idx_coupon_id` (`coupon_id`);
|
||
ALTER TABLE `order` ADD INDEX `idx_service_address_id` (`service_address_id`);
|
||
```
|
||
|
||
---
|
||
|
||
### 2. 优惠券表 (coupon)
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS `coupon` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '优惠券名称',
|
||
`type` TINYINT NOT NULL COMMENT '类型：1-满减券，2-折扣券，3-代金券',
|
||
`discount_type` TINYINT NOT NULL COMMENT '折扣类型：1-金额，2-折扣',
|
||
`discount_value` DECIMAL(10,2) NOT NULL COMMENT '折扣值（金额或折扣率）',
|
||
`min_amount` DECIMAL(10,2) DEFAULT 0.00 COMMENT '最低消费金额',
|
||
`max_discount` DECIMAL(10,2) COMMENT '最大折扣金额',
|
||
`total_quantity` INT NOT NULL COMMENT '发行总量',
|
||
`received_quantity` INT DEFAULT 0 COMMENT '已领取数量',
|
||
`used_quantity` INT DEFAULT 0 COMMENT '已使用数量',
|
||
`start_time` DATETIME NOT NULL COMMENT '开始时间',
|
||
`end_time` DATETIME NOT NULL COMMENT '结束时间',
|
||
`status` TINYINT DEFAULT 1 COMMENT '状态：0-停用，1-启用',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_start_end_time` (`start_time`, `end_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券表';
|
||
```
|
||
|
||
---
|
||
|
||
### 3. 用户优惠券表 (user_coupon)
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS `user_coupon` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
||
`coupon_id` BIGINT NOT NULL COMMENT '优惠券ID',
|
||
`order_id` BIGINT COMMENT '使用的订单ID',
|
||
`status` TINYINT DEFAULT 0 COMMENT '状态：0-未使用，1-已使用，2-已过期',
|
||
`receive_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '领取时间',
|
||
`use_time` DATETIME COMMENT '使用时间',
|
||
`expire_time` DATETIME NOT NULL COMMENT '过期时间',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_coupon_id` (`coupon_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_expire_time` (`expire_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户优惠券表';
|
||
```
|
||
|
||
---
|
||
|
||
### 4. 用户地址表 (user_address)
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS `user_address` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
||
`contact_name` VARCHAR(50) NOT NULL COMMENT '联系人姓名',
|
||
`contact_phone` VARCHAR(20) NOT NULL COMMENT '联系电话',
|
||
`province` VARCHAR(50) COMMENT '省份',
|
||
`city` VARCHAR(50) COMMENT '城市',
|
||
`district` VARCHAR(50) COMMENT '区县',
|
||
`address` VARCHAR(200) NOT NULL COMMENT '详细地址',
|
||
`address_tag` VARCHAR(20) COMMENT '地址标签：家，公司，学校',
|
||
`longitude` DECIMAL(10,6) COMMENT '经度',
|
||
`latitude` DECIMAL(10,6) COMMENT '纬度',
|
||
`is_default` TINYINT DEFAULT 0 COMMENT '是否默认地址：0-否，1-是',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_is_default` (`is_default`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户地址表';
|
||
```
|
||
|
||
---
|
||
|
||
### 5. 次卡表检查 (time_card)
|
||
|
||
检查现有字段是否完整：
|
||
|
||
```sql
|
||
-- 检查time_card表结构
|
||
SELECT
|
||
COLUMN_NAME,
|
||
COLUMN_TYPE,
|
||
IS_NULLABLE,
|
||
COLUMN_DEFAULT,
|
||
COLUMN_COMMENT
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'peixue'
|
||
AND TABLE_NAME = 'time_card'
|
||
ORDER BY ORDINAL_POSITION;
|
||
```
|
||
|
||
如果缺少字段，添加：
|
||
|
||
```sql
|
||
-- 添加缺失字段（如果不存在）
|
||
ALTER TABLE `time_card` ADD COLUMN IF NOT EXISTS `total_hours` INT NOT NULL DEFAULT 0 COMMENT '总时长（小时）';
|
||
ALTER TABLE `time_card` ADD COLUMN IF NOT EXISTS `used_hours` INT NOT NULL DEFAULT 0 COMMENT '已使用时长（小时）';
|
||
ALTER TABLE `time_card` ADD COLUMN IF NOT EXISTS `remaining_hours` INT NOT NULL DEFAULT 0 COMMENT '剩余时长（小时）';
|
||
ALTER TABLE `time_card` ADD COLUMN IF NOT EXISTS `status` INT DEFAULT 0 COMMENT '状态：0-未激活，1-进行中，2-已完成，3-已过期';
|
||
ALTER TABLE `time_card` ADD COLUMN IF NOT EXISTS `expire_date` DATE COMMENT '过期日期';
|
||
```
|
||
|
||
---
|
||
|
||
### 6. 套餐表检查 (package)
|
||
|
||
检查现有字段是否完整：
|
||
|
||
```sql
|
||
-- 检查package表结构
|
||
SELECT
|
||
COLUMN_NAME,
|
||
COLUMN_TYPE,
|
||
IS_NULLABLE,
|
||
COLUMN_DEFAULT,
|
||
COLUMN_COMMENT
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'peixue'
|
||
AND TABLE_NAME = 'package'
|
||
ORDER BY ORDINAL_POSITION;
|
||
```
|
||
|
||
---
|
||
|
||
### 7. 用户套餐表 (user_package)
|
||
|
||
检查是否存在，如不存在则创建：
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS `user_package` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
||
`package_id` BIGINT NOT NULL COMMENT '套餐ID',
|
||
`package_name` VARCHAR(100) NOT NULL COMMENT '套餐名称',
|
||
`package_type` VARCHAR(50) COMMENT '套餐类型',
|
||
`total_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '总课时',
|
||
`used_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '已使用课时',
|
||
`remaining_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '剩余课时',
|
||
`service_count` INT DEFAULT 0 COMMENT '服务次数',
|
||
`used_count` INT DEFAULT 0 COMMENT '已使用次数',
|
||
`remaining_count` INT DEFAULT 0 COMMENT '剩余次数',
|
||
`price` DECIMAL(10,2) COMMENT '购买价格',
|
||
`purchase_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '购买时间',
|
||
`start_date` DATE COMMENT '开始日期',
|
||
`expire_date` DATE COMMENT '过期日期',
|
||
`status` TINYINT DEFAULT 1 COMMENT '状态：0-已过期，1-使用中，2-已用完',
|
||
`version` INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_package_id` (`package_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_expire_date` (`expire_date`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户套餐表';
|
||
```
|
||
|
||
---
|
||
|
||
## 📋 完整SQL脚本
|
||
|
||
创建一个完整的SQL脚本文件：`plan_a_payment_system_database.sql`
|
||
|
||
```sql
|
||
-- ========================================
|
||
-- 方案A：其他支付方式数据库脚本
|
||
-- 创建日期：2026-01-23
|
||
-- 数据库：peixue
|
||
-- ========================================
|
||
|
||
USE peixue;
|
||
|
||
-- ========================================
|
||
-- 1. 扩展订单表
|
||
-- ========================================
|
||
|
||
-- 检查并添加支付方式相关字段
|
||
SET @dbname = DATABASE();
|
||
SET @tablename = 'order';
|
||
SET @columnname = 'payment_method';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(
|
||
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE
|
||
(TABLE_SCHEMA = @dbname)
|
||
AND (TABLE_NAME = @tablename)
|
||
AND (COLUMN_NAME = @columnname)
|
||
) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' VARCHAR(50) COMMENT ''支付方式：wallet,timecard,package,wechat,mixed'' AFTER pay_status')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 添加其他字段（使用相同的模式）
|
||
-- wallet_amount
|
||
SET @columnname = 'wallet_amount';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' DECIMAL(10,2) DEFAULT 0.00 COMMENT ''钱包支付金额''')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- timecard_id
|
||
SET @columnname = 'timecard_id';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT COMMENT ''使用的次卡ID''')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- package_id
|
||
SET @columnname = 'package_id';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT COMMENT ''使用的套餐ID''')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- coupon_id
|
||
SET @columnname = 'coupon_id';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT COMMENT ''使用的优惠券ID''')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- coupon_discount
|
||
SET @columnname = 'coupon_discount';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' DECIMAL(10,2) DEFAULT 0.00 COMMENT ''优惠券折扣金额''')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- service_address_id
|
||
SET @columnname = 'service_address_id';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' BIGINT COMMENT ''服务地址ID''')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- original_amount
|
||
SET @columnname = 'original_amount';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' DECIMAL(10,2) COMMENT ''原价'' AFTER pay_amount')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- discount_amount
|
||
SET @columnname = 'discount_amount';
|
||
SET @preparedStatement = (SELECT IF(
|
||
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
|
||
'SELECT 1',
|
||
CONCAT('ALTER TABLE ', @tablename, ' ADD COLUMN ', @columnname, ' DECIMAL(10,2) DEFAULT 0.00 COMMENT ''总折扣金额''')
|
||
));
|
||
PREPARE alterIfNotExists FROM @preparedStatement;
|
||
EXECUTE alterIfNotExists;
|
||
DEALLOCATE PREPARE alterIfNotExists;
|
||
|
||
-- 添加索引
|
||
ALTER TABLE `order` ADD INDEX IF NOT EXISTS `idx_timecard_id` (`timecard_id`);
|
||
ALTER TABLE `order` ADD INDEX IF NOT EXISTS `idx_package_id` (`package_id`);
|
||
ALTER TABLE `order` ADD INDEX IF NOT EXISTS `idx_coupon_id` (`coupon_id`);
|
||
ALTER TABLE `order` ADD INDEX IF NOT EXISTS `idx_service_address_id` (`service_address_id`);
|
||
|
||
-- ========================================
|
||
-- 2. 创建优惠券表
|
||
-- ========================================
|
||
|
||
CREATE TABLE IF NOT EXISTS `coupon` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`name` VARCHAR(100) NOT NULL COMMENT '优惠券名称',
|
||
`type` TINYINT NOT NULL COMMENT '类型：1-满减券，2-折扣券，3-代金券',
|
||
`discount_type` TINYINT NOT NULL COMMENT '折扣类型：1-金额，2-折扣',
|
||
`discount_value` DECIMAL(10,2) NOT NULL COMMENT '折扣值（金额或折扣率）',
|
||
`min_amount` DECIMAL(10,2) DEFAULT 0.00 COMMENT '最低消费金额',
|
||
`max_discount` DECIMAL(10,2) COMMENT '最大折扣金额',
|
||
`total_quantity` INT NOT NULL COMMENT '发行总量',
|
||
`received_quantity` INT DEFAULT 0 COMMENT '已领取数量',
|
||
`used_quantity` INT DEFAULT 0 COMMENT '已使用数量',
|
||
`start_time` DATETIME NOT NULL COMMENT '开始时间',
|
||
`end_time` DATETIME NOT NULL COMMENT '结束时间',
|
||
`status` TINYINT DEFAULT 1 COMMENT '状态：0-停用，1-启用',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_start_end_time` (`start_time`, `end_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券表';
|
||
|
||
-- ========================================
|
||
-- 3. 创建用户优惠券表
|
||
-- ========================================
|
||
|
||
CREATE TABLE IF NOT EXISTS `user_coupon` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
||
`coupon_id` BIGINT NOT NULL COMMENT '优惠券ID',
|
||
`order_id` BIGINT COMMENT '使用的订单ID',
|
||
`status` TINYINT DEFAULT 0 COMMENT '状态：0-未使用，1-已使用，2-已过期',
|
||
`receive_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '领取时间',
|
||
`use_time` DATETIME COMMENT '使用时间',
|
||
`expire_time` DATETIME NOT NULL COMMENT '过期时间',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_coupon_id` (`coupon_id`),
|
||
KEY `idx_order_id` (`order_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_expire_time` (`expire_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户优惠券表';
|
||
|
||
-- ========================================
|
||
-- 4. 创建用户地址表
|
||
-- ========================================
|
||
|
||
CREATE TABLE IF NOT EXISTS `user_address` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
||
`contact_name` VARCHAR(50) NOT NULL COMMENT '联系人姓名',
|
||
`contact_phone` VARCHAR(20) NOT NULL COMMENT '联系电话',
|
||
`province` VARCHAR(50) COMMENT '省份',
|
||
`city` VARCHAR(50) COMMENT '城市',
|
||
`district` VARCHAR(50) COMMENT '区县',
|
||
`address` VARCHAR(200) NOT NULL COMMENT '详细地址',
|
||
`address_tag` VARCHAR(20) COMMENT '地址标签：家，公司，学校',
|
||
`longitude` DECIMAL(10,6) COMMENT '经度',
|
||
`latitude` DECIMAL(10,6) COMMENT '纬度',
|
||
`is_default` TINYINT DEFAULT 0 COMMENT '是否默认地址：0-否，1-是',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_is_default` (`is_default`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户地址表';
|
||
|
||
-- ========================================
|
||
-- 5. 创建用户套餐表
|
||
-- ========================================
|
||
|
||
CREATE TABLE IF NOT EXISTS `user_package` (
|
||
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`user_id` BIGINT NOT NULL COMMENT '用户ID',
|
||
`package_id` BIGINT NOT NULL COMMENT '套餐ID',
|
||
`package_name` VARCHAR(100) NOT NULL COMMENT '套餐名称',
|
||
`package_type` VARCHAR(50) COMMENT '套餐类型',
|
||
`total_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '总课时',
|
||
`used_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '已使用课时',
|
||
`remaining_hours` DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '剩余课时',
|
||
`service_count` INT DEFAULT 0 COMMENT '服务次数',
|
||
`used_count` INT DEFAULT 0 COMMENT '已使用次数',
|
||
`remaining_count` INT DEFAULT 0 COMMENT '剩余次数',
|
||
`price` DECIMAL(10,2) COMMENT '购买价格',
|
||
`purchase_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '购买时间',
|
||
`start_date` DATE COMMENT '开始日期',
|
||
`expire_date` DATE COMMENT '过期日期',
|
||
`status` TINYINT DEFAULT 1 COMMENT '状态：0-已过期，1-使用中，2-已用完',
|
||
`version` INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
|
||
`tenant_id` BIGINT COMMENT '租户ID',
|
||
`created_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT DEFAULT 0 COMMENT '删除标记',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_user_id` (`user_id`),
|
||
KEY `idx_package_id` (`package_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_expire_date` (`expire_date`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户套餐表';
|
||
|
||
-- ========================================
|
||
-- 完成
|
||
-- ========================================
|
||
|
||
SELECT '✅ 方案A数据库脚本执行完成' AS message;
|
||
```
|
||
|
||
---
|
||
|
||
## 🔍 数据完整性约束
|
||
|
||
### 外键关系（逻辑外键，不使用物理外键）
|
||
|
||
```
|
||
order.timecard_id → time_card.id
|
||
order.package_id → user_package.id
|
||
order.coupon_id → user_coupon.id
|
||
order.service_address_id → user_address.id
|
||
|
||
user_coupon.coupon_id → coupon.id
|
||
user_coupon.order_id → order.id
|
||
|
||
user_package.package_id → package.id
|
||
```
|
||
|
||
### 数据一致性规则
|
||
|
||
1. **优惠券使用一致性**
|
||
```
|
||
user_coupon.status = 1 时，order_id 不能为空
|
||
user_coupon.use_time 不能为空
|
||
```
|
||
|
||
2. **地址默认唯一性**
|
||
```
|
||
每个用户只能有一个默认地址（is_default = 1）
|
||
```
|
||
|
||
3. **订单金额一致性**
|
||
```
|
||
order.pay_amount = order.original_amount - order.discount_amount
|
||
order.discount_amount = order.coupon_discount
|
||
```
|
||
|
||
---
|
||
|
||
## 📝 下一步
|
||
|
||
1. 执行SQL脚本创建/更新表结构
|
||
2. 验证数据完整性
|
||
3. 开始后端接口实现
|
||
|
||
---
|
||
|
||
**文档版本**: v1.0
|
||
**最后更新**: 2026-01-23
|