-- 以下设计意在统一各类优惠活动(满减 | 满送 | 限时购 | 阶梯价)
该设计可能存在不足,请留言告诉我,谢谢
-- 以下设计意在统一各类优惠活动(满减 | 满送 | 限时购 | 阶梯价)
CREATE TABLE `t_activity` (
`id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT 'PK',
`superior_code` varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT '上级编号',
`platform_code` varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT '平台编号',
`shop_id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '店铺Id',
`activity_type` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '活动类型: 满减 | 满送 | 限时购(每种活动类型同一时期(每店或平台)只能启动一个)',
`belong` int(1) COLLATE utf8mb4_bin NOT NULL DEFAULT '0' COMMENT '券所属(新增后不可修改):0平台券(APP首页查询) 1店铺券(该券包含商品券,店铺首页查询或商品详细查询) 2个人券(APP首页查询)',
`take_type` int(1) COLLATE utf8mb4_bin NOT NULL DEFAULT '0' COMMENT '领取方式:0无需领取 1用户领取(界面可查待领取) 2分享领取(通过分享二维码或小程序码的方式发放给用户) 3后台发放(创建活动后使用单独的发放界面规则去发放)',
`name` varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '活动名称:618活动大促 满300减30',
`tags` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '活动标签:满减 满送 限时',
`icon` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '图标',
`banner` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT 'banner',
`rules` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '领取规则配置(用于校验用户是否符合领取规则):[]',
`total_count` int(11) NOT NULL DEFAULT '1' COMMENT '总数量',
`take_count` int(11) NOT NULL DEFAULT '1' COMMENT '已领取数量',
`use_count` int(11) NOT NULL DEFAULT '1' COMMENT '已使用数量',
`limited` int(11) NOT NULL DEFAULT '1' COMMENT '限领取数量',
`start_time` datetime NOT NULL COMMENT '活动开始时间',
`end_time` datetime NOT NULL COMMENT '活动结束时间',
`effective_minutes` datetime NOT NULL COMMENT '自活动结束后有效分钟数(不得小于5分钟)',
`status` int(1) NOT NULL DEFAULT '0' COMMENT '活动状态:0待发布 1已发布 2已暂停',
`deleted` int(1) NOT NULL DEFAULT '0',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`create_by` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`update_by` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `platform_code` (`platform_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='活动表';
CREATE TABLE `t_activity_detail` (
`id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT 'PK',
`superior_code` varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT '上级编号',
`platform_code` varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT '平台编号',
`activity_id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '活动ID',
`belong` int(1) COLLATE utf8mb4_bin NOT NULL DEFAULT '0' COMMENT '券所属:0平台券(APP首页查询) 1店铺券(店铺首页查询) 2个人券(APP首页查询)',
`data_id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '指定店铺Id(店铺券)/商品ID(商品券)/用户ID(用户券)',
-- `detail_pic` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '活动图片(可单独设置)',
`status` int(11) NOT NULL DEFAULT '1' COMMENT '状态:0正常 1作废',
`deleted` int(1) NOT NULL DEFAULT '0',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`create_by` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`update_by` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `platform_code` (`platform_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='活动详细表(需要领取的进入这个表)';
CREATE TABLE `t_activity_take_record` (
`id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT 'PK',
`superior_code` varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT '上级编号',
`platform_code` varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT '平台编号',
`user_id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '领取用户ID',
`activity_id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '活动ID',
`belong` int(1) COLLATE utf8mb4_bin NOT NULL DEFAULT '0' COMMENT '券所属:0平台券(APP首页查询) 1店铺券(店铺首页查询) 2个人券(APP首页查询)',
`data_id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '指定店铺Id(店铺券)/商品ID(商品券)/用户ID(用户券)',
`status` int(11) NOT NULL DEFAULT '0' COMMENT '状态:0正常 1已使用 2作废',
`deleted` int(1) NOT NULL DEFAULT '0',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`create_by` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`update_by` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `platform_code` (`platform_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='领取记录表';
CREATE TABLE `t_activity_task_record` (
`id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT 'PK',
`superior_code` varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT '上级编号',
`platform_code` varchar(32) CHARACTER SET utf8mb4 NOT NULL COMMENT '平台编号',
`user_id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '领取用户ID',
`take_record_id` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '领取记录ID',
`order_no` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '订单编号',
`status` int(11) NOT NULL DEFAULT '1' COMMENT '状态:0已处理 1待处理',
`deleted` int(1) NOT NULL DEFAULT '0',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`create_by` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`update_by` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
KEY `platform_code` (`platform_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='活动任务记录表(用于异步任务,如满送确认收货后送)';