38 lines
2.2 KiB
SQL
38 lines
2.2 KiB
SQL
DROP TABLE IF EXISTS `ab98_user_vip`;
|
||
|
||
CREATE TABLE `ab98_user_vip` (
|
||
`vip_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`ab98_user_id` BIGINT NOT NULL COMMENT '关联用户ID',
|
||
`vip_level` TINYINT NOT NULL DEFAULT 1 COMMENT 'VIP等级',
|
||
`start_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '会员开始时间',
|
||
`end_time` DATETIME NULL COMMENT '会员结束时间',
|
||
`status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '状态（1有效 0无效）',
|
||
`creator_id` BIGINT DEFAULT 0 COMMENT '创建者ID',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` BIGINT DEFAULT 0 COMMENT '更新者ID',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`vip_id`),
|
||
KEY `idx_user` (`ab98_user_id`),
|
||
KEY `idx_level` (`vip_level`),
|
||
CONSTRAINT `fk_vip_user` FOREIGN KEY (`ab98_user_id`) REFERENCES `ab98_user` (`ab98_user_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户VIP信息表';
|
||
|
||
DROP TABLE IF EXISTS `ab98_vip_level`;
|
||
|
||
CREATE TABLE `ab98_vip_level` (
|
||
`level_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
||
`level_name` VARCHAR(50) NOT NULL COMMENT '等级名称',
|
||
`level_value` TINYINT NOT NULL COMMENT '等级值',
|
||
`discount_rate` DECIMAL(5,2) NOT NULL COMMENT '折扣率',
|
||
`point_multiplier` DECIMAL(5,2) NOT NULL COMMENT '积分倍数',
|
||
`free_shipping` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否免邮(0否 1是)',
|
||
`birthday_benefit` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '生日特权(0无 1有)',
|
||
`creator_id` BIGINT DEFAULT 0 COMMENT '创建者ID',
|
||
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updater_id` BIGINT DEFAULT 0 COMMENT '更新者ID',
|
||
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
`deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '删除标志（0存在 1删除）',
|
||
PRIMARY KEY (`level_id`),
|
||
UNIQUE KEY `uk_level` (`level_value`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='VIP等级权限表'; |