【【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='配送方式';
