39 lines
2.1 KiB
SQL
39 lines
2.1 KiB
SQL
-- 订单表
|
||
CREATE TABLE IF NOT EXISTS `la_order` (
|
||
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`order_no` varchar(50) NOT NULL COMMENT '订单号',
|
||
`patient_id` int(11) NOT NULL COMMENT '患者ID',
|
||
`creator_id` int(11) NOT NULL COMMENT '创建人ID（推广ID）',
|
||
`order_type` tinyint(1) NOT NULL COMMENT '订单类型 1-挂号费 2-问诊费 3-药品费用',
|
||
`amount` decimal(10, 2) NOT NULL COMMENT '订单金额',
|
||
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '订单状态 1-待支付 2-已支付 3-已取消 4-已退款',
|
||
`payment_method` varchar(20) DEFAULT NULL COMMENT '支付方式 alipay-支付宝 wechat-微信',
|
||
`payment_time` datetime DEFAULT NULL COMMENT '支付时间',
|
||
`trade_no` varchar(100) DEFAULT NULL COMMENT '第三方交易号',
|
||
`remark` varchar(500) DEFAULT '' COMMENT '备注',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`delete_time` datetime DEFAULT NULL COMMENT '删除时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_order_no` (`order_no`),
|
||
KEY `idx_patient` (`patient_id`),
|
||
KEY `idx_creator` (`creator_id`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_create_time` (`create_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
|
||
|
||
-- 订单详情表（关联挂号、问诊等）
|
||
CREATE TABLE IF NOT EXISTS `la_order_detail` (
|
||
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`order_id` int(11) NOT NULL COMMENT '订单ID',
|
||
`related_type` varchar(20) NOT NULL COMMENT '关联类型 appointment-挂号 diagnosis-问诊 medicine-药品',
|
||
`related_id` int(11) NOT NULL COMMENT '关联ID',
|
||
`quantity` int(11) DEFAULT '1' COMMENT '数量',
|
||
`unit_price` decimal(10, 2) NOT NULL COMMENT '单价',
|
||
`total_price` decimal(10, 2) NOT NULL COMMENT '总价',
|
||
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_order` (`order_id`),
|
||
KEY `idx_related` (`related_type`, `related_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单详情表';
|