商城会员卡数据库表设计之MySQL

会员卡表

会员卡配置表

用户购买会员卡表

用户领取优惠券记录表

sql脚本

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;
-- ---------------------------- -- Table structure for mcs_member_card -- ----------------------------
DROP TABLE
IF
	EXISTS `mcs_member_card`;
CREATE TABLE `mcs_member_card` (
	`id` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'guid主键',
	`site_id` INT ( 10 ) NULL DEFAULT NULL COMMENT '站点',
	`member_level` TINYINT ( 4 ) NULL DEFAULT NULL COMMENT '会员级别',
	`card_image` VARCHAR ( 255 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '默认图片',
	`card_amount` BIGINT ( 20 ) NULL DEFAULT NULL COMMENT '会员卡金额',
	`card_name` VARCHAR ( 100 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '会员卡名称',
	`card_subtitle` VARCHAR ( 255 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '会员卡副标题',
	`valid_time_unit` TINYINT ( 4 ) NOT NULL COMMENT '有效时长单位，周，月，季，年',
	`valid_time` INT ( 10 ) NOT NULL COMMENT '有效时长(周期)，表示几周，几个月',
	`starttime` datetime ( 0 ) NULL DEFAULT NULL COMMENT '有效期开始日期',
	`endtime` datetime ( 0 ) NULL DEFAULT NULL COMMENT '有效期结束日期',
	`json_remark` VARCHAR ( 2000 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'JSON备注',
	`card_explain` VARCHAR ( 2000 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '会员卡使用说明',
	`remark` text CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL COMMENT '备注',
	`state` TINYINT ( 4 ) NULL DEFAULT NULL COMMENT '状态',
	`addtime` datetime ( 0 ) NULL DEFAULT NULL COMMENT '添加日期',
	PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ---------------------------- -- Table structure for mcs_membercard_config -- ----------------------------
DROP TABLE
IF
	EXISTS `mcs_membercard_config`;
CREATE TABLE `mcs_membercard_config` (
	`id` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'guid主键',
	`mcard_id` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联会员卡ID',
	`ref_type` TINYINT ( 4 ) NULL DEFAULT NULL COMMENT '关联类型',
	`ref_id` VARCHAR ( 200 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联ID',
	`img_url` VARCHAR ( 255 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '图片地址',
	`count` INT ( 10 ) NULL DEFAULT NULL COMMENT '数量',
	`time_interval` INT ( 10 ) NULL DEFAULT NULL COMMENT '间隔天数',
	`valid_time` INT ( 10 ) NULL DEFAULT NULL COMMENT '有效天数',
	`json_remark` VARCHAR ( 2000 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'JSON备注',
	`state` TINYINT ( 1 ) NULL DEFAULT 0 COMMENT '状态',
	`addtime` datetime ( 0 ) NULL DEFAULT NULL COMMENT '添加时间',
	PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ---------------------------- -- Table structure for mcs_membercard_log -- ----------------------------
DROP TABLE
IF
	EXISTS `mcs_membercard_log`;
CREATE TABLE `mcs_membercard_log` (
	`id` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'guid主键',
	`task_id` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '来源任务ID 如：用户会员卡ID ',
	`user_id` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联用户ID',
	`ref_type` TINYINT ( 4 ) NULL DEFAULT NULL COMMENT '来源类型 如 产品，优惠券',
	`ref_id` VARCHAR ( 100 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '来源ID',
	`get_count` INT ( 10 ) NULL DEFAULT NULL COMMENT '领取数量',
	`remark` text CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL COMMENT '备注',
	`addtime` datetime ( 0 ) NULL DEFAULT NULL COMMENT '添加日期',
	PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ---------------------------- -- Table structure for mcs_membercard_user -- ----------------------------
DROP TABLE
IF
	EXISTS `mcs_membercard_user`;
CREATE TABLE `mcs_membercard_user` (
	`id` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'guid主键',
	`mcard_id` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联会员卡ID',
	`card_num` VARCHAR ( 100 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '会员卡编号',
	`user_id` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户ID',
	`starttime` datetime ( 0 ) NULL DEFAULT NULL COMMENT '有效期开始日期',
	`endtime` datetime ( 0 ) NULL DEFAULT NULL COMMENT '有效期结束日期',
	`ref_order_id` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联订单id',
	`ref_order_code` VARCHAR ( 100 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联订单号',
	`json_remark` VARCHAR ( 2000 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'JSON备注',
	`status` TINYINT ( 4 ) NULL DEFAULT NULL COMMENT '会员卡状态',
	`addtime` datetime ( 0 ) NULL DEFAULT NULL COMMENT '添加日期',
	PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ---------------------------- -- Table structure for mcs_membercard_user_coupons -- ----------------------------
DROP TABLE
IF
	EXISTS `mcs_membercard_user_coupons`;
CREATE TABLE `mcs_membercard_user_coupons` (
	`id` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '主键',
	`mcard_id` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '会员卡id',
	`coupon_id` VARCHAR ( 100 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '优惠券id',
	`uid` CHAR ( 36 ) CHARACTER 
	SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户id',
	`count` INT ( 10 ) NULL DEFAULT NULL COMMENT '数量',
	`status` TINYINT ( 4 ) NULL DEFAULT NULL COMMENT '优惠券状态99未领取，100已领取，110已失效',
	`batchnum` INT ( 10 ) NULL DEFAULT NULL COMMENT '批次',
	`valid_days` INT ( 10 ) NULL DEFAULT NULL COMMENT '有效天数',
	`gettime` datetime ( 0 ) NULL DEFAULT NULL COMMENT '领取时间',
	`starttime` datetime ( 0 ) NULL DEFAULT NULL COMMENT '有效开始时间',
	`endtime` datetime ( 0 ) NULL DEFAULT NULL COMMENT '有效结束时间',
	`addtime` datetime ( 0 ) NULL DEFAULT NULL COMMENT '创建时间',
	PRIMARY KEY ( `id` ) USING BTREE 
) ENGINE = INNODB CHARACTER 
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

如果文章对您有帮助，请您用发财的小手一键三连，您的支持是我创作的动力，感谢支持！！