【v6】微信小程序拼团数据库设计规范 mysqlphp

【【v6】微信小程序拼团数据库设计规范】来源：https://pinapp.gitee.io

基于拼团后端接口简约的特点，我们拟定了后端小程序拼团接口设计指南和建议。适合 0 - 1 构建拼团商城不错参考。

地址表

CREATE TABLE `address` ( `address_id` int(11) NOT NULL AUTO_INCREMENT, `member_id` int(11) NOT NULL, `country` int(11) DEFAULT NULL, `province` varchar(50) CHARACTER SET gbk NOT NULL DEFAULT '', `city` varchar(50) CHARACTER SET gbk NOT NULL, `district` varchar(50) CHARACTER SET gbk NOT NULL, `province_id` int(11) NOT NULL, `city_id` int(11) NOT NULL, `district_id` int(11) NOT NULL, `address` varchar(255) NOT NULL DEFAULT '', `id_number` varchar(50) CHARACTER SET gbk DEFAULT '', `mobile` varchar(11) CHARACTER SET gbk NOT NULL, `receive_name` varchar(20) CHARACTER SET gbk NOT NULL, `address_name` char(10) CHARACTER SET gbk DEFAULT '', `full_address` varchar(255) NOT NULL DEFAULT '', `status` varchar(20) CHARACTER SET gbk DEFAULT '', `created_time` int(10) NOT NULL, `last_updated_time` int(10) NOT NULL, PRIMARY KEY (`address_id`), KEY `member_id` (`member_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='地址';

附件表

CREATE TABLE `attachment` ( `attach_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `attach_url` varchar(255) NOT NULL DEFAULT '', `oss` tinyint(1) unsigned NOT NULL, `time` int(10) unsigned NOT NULL, PRIMARY KEY (`attach_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='附件表';

商品表

CREATE TABLE `goods` ( `goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `cate_id` int(11) unsigned NOT NULL COMMENT '分类ID', `goods_name` varchar(50) NOT NULL DEFAULT '' COMMENT '物品名称', `image_url` varchar(255) NOT NULL DEFAULT '' COMMENT '主图', `goods_desc` mediumtext NOT NULL COMMENT '物品描述', `goods_imgs` text NOT NULL COMMENT '商品图片', `market_price` decimal(10,2) unsigned NOT NULL COMMENT '市场价', `group_price` decimal(10,2) unsigned NOT NULL COMMENT '团购价', `alone_price` double(10,2) unsigned NOT NULL COMMENT '单独购买价格', `group_number` int(11) unsigned NOT NULL COMMENT '团购人数', `sell_count` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '销售数量', `limit_buy` tinyint(1) unsigned NOT NULL COMMENT '一个会员限制购买几次', `goods_stock` int(11) unsigned NOT NULL COMMENT '库存', `in_selling` tinyint(1) unsigned NOT NULL COMMENT '是否上架', `goods_sort` smallint(5) NOT NULL COMMENT '排序', `sell_type` tinyint(1) unsigned NOT NULL COMMENT '0不限 1：只团购 2：只单买', `time` int(10) unsigned NOT NULL COMMENT '添加时间', `spec_name` varchar(255) NOT NULL DEFAULT '' COMMENT '商品规格', `spec_type` tinyint(1) unsigned NOT NULL COMMENT '0单规格 1：多规格', PRIMARY KEY (`goods_id`), KEY `cate_id` (`cate_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='物品表';

商品分类表

CREATE TABLE `goods_cate` ( `cate_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `cate_name` varchar(50) NOT NULL DEFAULT '', `parent_cate` int(11) unsigned NOT NULL, `sort` int(11) NOT NULL, PRIMARY KEY (`cate_id`), KEY `parent_cate` (`parent_cate`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='物品分类';

商品规格表

CREATE TABLE `goods_sku` ( `sku_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `goods_id` int(11) unsigned NOT NULL, `thumb_url` varchar(200) CHARACTER SET gbk DEFAULT '', `alone_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '单独购买', `group_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '团购价格', `goods_stock` int(11) NOT NULL DEFAULT '0', `sell_count` int(11) unsigned NOT NULL DEFAULT '0', `goods_spec` varchar(255) NOT NULL DEFAULT '', `sort` int(11) NOT NULL, PRIMARY KEY (`sku_id`), KEY `goods_id` (`goods_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

拼团表

CREATE TABLE `group` ( `group_order_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `require_num` int(11) unsigned NOT NULL, `people` int(5) unsigned NOT NULL, `status` tinyint(1) unsigned NOT NULL, `create_time` int(10) unsigned NOT NULL, `expire_time` int(10) unsigned NOT NULL, `success_time` int(10) unsigned DEFAULT NULL, `owner_id` int(10) unsigned NOT NULL, `notify` tinyint(1) unsigned DEFAULT NULL, PRIMARY KEY (`group_order_id`), KEY `owner_id` (`owner_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

会员表

CREATE TABLE `member` ( `member_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `open_id` varchar(255) NOT NULL DEFAULT '' COMMENT '用户open_id', `unionid` varchar(255) NOT NULL DEFAULT '', `nickname` varchar(50) NOT NULL DEFAULT '' COMMENT '用户昵称', `subscribe` tinyint(1) unsigned NOT NULL COMMENT '是否关注', `sex` tinyint(1) unsigned DEFAULT NULL COMMENT '性别', `headimgurl` varchar(255) NOT NULL DEFAULT '' COMMENT '头像', `disablle` tinyint(1) unsigned NOT NULL COMMENT '是否禁用', `time` int(10) unsigned NOT NULL COMMENT '注册时间', PRIMARY KEY (`member_id`), KEY `open_id` (`open_id`(250)), KEY `unionid` (`unionid`(250)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='会员表';

会员令牌表

CREATE TABLE `member_token` ( `member_id` int(11) unsigned NOT NULL, `token` varchar(48) NOT NULL DEFAULT '', `expires` int(10) unsigned NOT NULL, UNIQUE KEY `token` (`token`), KEY `member_id` (`member_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

订单日志表

CREATE TABLE `order_log` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `manage` varchar(50) NOT NULL DEFAULT '', `order_id` int(11) unsigned NOT NULL, `remark` mediumtext NOT NULL, `time` int(10) unsigned NOT NULL, `do` varchar(50) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `order_id` (`order_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='订单日志';

订单表

CREATE TABLE `orders` ( `order_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `buyer_id` int(11) unsigned NOT NULL, `order_sn` varchar(50) NOT NULL DEFAULT '', `goods_id` int(11) unsigned NOT NULL, `group_order_id` int(11) unsigned NOT NULL, `group_header` tinyint(1) unsigned DEFAULT NULL, `pay_id` int(11) unsigned DEFAULT NULL, `pay_sn` varchar(50) NOT NULL DEFAULT '', `group_buy` tinyint(1) unsigned NOT NULL, `province_id` int(11) unsigned NOT NULL, `province_name` varchar(50) NOT NULL DEFAULT '', `city_id` int(11) unsigned NOT NULL, `city_name` varchar(50) NOT NULL DEFAULT '', `district_id` int(11) unsigned NOT NULL, `district_name` varchar(50) NOT NULL DEFAULT '', `mobile` char(11) NOT NULL DEFAULT '', `receive_name` varchar(11) NOT NULL DEFAULT '', `nickname` varchar(25) NOT NULL DEFAULT '', `order_goods` mediumtext NOT NULL, `goods_amount` double(10,2) unsigned NOT NULL, `order_amount` double(10,2) unsigned NOT NULL, `pay_amount` double(10,2) unsigned NOT NULL, `shipping_address` varchar(255) NOT NULL DEFAULT '', `shipping_amount` double(10,2) unsigned NOT NULL, `shipping_time` int(10) unsigned DEFAULT NULL, `shipping_name` varchar(20) NOT NULL DEFAULT '', `shipping_code` varchar(30) NOT NULL DEFAULT '', `tracking_number` varchar(80) NOT NULL DEFAULT '', `order_status` tinyint(1) unsigned NOT NULL, `order_time` int(10) unsigned NOT NULL, `pay_time` int(10) unsigned DEFAULT NULL, `received_time` int(10) unsigned DEFAULT NULL, `goods_number` int(11) unsigned NOT NULL, `sku_id` int(11) unsigned DEFAULT NULL, `goods_sku` text, PRIMARY KEY (`order_id`), KEY `goods_id` (`goods_id`), KEY `order_sn` (`order_sn`), KEY `member_id` (`buyer_id`), KEY `group_order_id` (`group_order_id`), KEY `pay_id` (`pay_id`), KEY `pay_sn` (`pay_sn`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

支付日志表

CREATE TABLE `paylog` ( `pay_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pay_sn` varchar(50) NOT NULL DEFAULT '', `order_id` int(11) unsigned NOT NULL, `pay_amount` decimal(10,2) unsigned NOT NULL, `pay_start_time` int(10) unsigned NOT NULL, `pay_done_time` int(10) unsigned DEFAULT NULL, `transaction_id` varchar(50) NOT NULL DEFAULT '' COMMENT '支付订单号', `refound_id` int(11) unsigned DEFAULT NULL COMMENT '退款订单号', `pay_type` varchar(50) NOT NULL DEFAULT '', `pay_status` tinyint(1) unsigned NOT NULL, `prepay_id` varchar(50) DEFAULT NULL, PRIMARY KEY (`pay_id`), KEY `pay_sn` (`pay_sn`), KEY `order_id` (`order_id`), KEY `refound_id` (`refound_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

地区表

CREATE TABLE `region` ( `region_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `region_name` varchar(50) NOT NULL DEFAULT '', `parent_id` int(11) unsigned NOT NULL, `status` tinyint(1) unsigned NOT NULL DEFAULT '2', `level` tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`region_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='配送地区';

分享表

CREATE TABLE `share` ( `share_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `member_id` int(11) unsigned NOT NULL, `share_type` varchar(50) NOT NULL DEFAULT '', `share_status` tinyint(1) unsigned NOT NULL DEFAULT '0', `share_time` int(11) unsigned NOT NULL, `share_url` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`share_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='分享数据';

物流表

CREATE TABLE `shipping` ( `shipping_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `shipping_name` varchar(50) NOT NULL DEFAULT '' COMMENT '物流名称', `shipping_code` varchar(50) NOT NULL DEFAULT '', PRIMARY KEY (`shipping_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='配送方式';