渐进式可扩展数据库模型(Progressive Extensible Database Model, pedm)

渐进式可扩展数据库模型(Progressive Extensible Database Model, pedm)

常用字段综合演示表

-- ----------------------------
-- Table structure for tbl_sample
-- [MySQL 5.7 GIS特性] https://www.jianshu.com/p/8833f68c3026
-- BLOB, TEXT, GEOMETRY or JSON column can't have a default value
-- ----------------------------
DROP TABLE IF EXISTS `tbl_sample`;

CREATE TABLE `tbl_sample` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `pid` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级ID(下拉单选) [cdl:component=select]',
  `fk_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '外键ID(下拉单选可分页) [cdl:component=selectpage]',
  `fk_ids` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '外键ID(下拉多选) [cdl:component=selectmultiple]',
  `week` ENUM('unkonw','mon', 'tues', 'wed', 'thur', 'fri', 'sat', 'sun') NOT NULL DEFAULT 'unkonw' COMMENT '星期(下拉单选) [cdl:component=select]:unkonw=未知,mon=星期一,tues=星期二,wed=星期三,thur=星期四,fri=星期五,sta=星期六,sun=星期天',
  `gender` ENUM('male', 'female') NOT NULL DEFAULT 'male' COMMENT '性别(单选) [cdl:component=radio]:male=男,female=女',
  `hobby` SET('unkonw','music', 'reading', 'swimming') NOT NULL DEFAULT '' COMMENT '爱好(多选) [cdl:component=checkbox]:unkonw=未知,music=音乐,reading=读书,swimming=游泳',
  `title` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '标题 [cdl:search==,like]',
  `password` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '密码 [cdl:component=passwordbox]',
  `color` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '颜色 [cdl:component=color]',
  `bankcardno` VARCHAR(19) NOT NULL DEFAULT '' COMMENT '银行卡号 [cdl:component=bankcardno] [cdl:search]',
  `image` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '图片 [cdl:component=imagepicker]',
  `images` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '图片组 [cdl:component=imagespicker]',
  `attachfile` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '附件 [cdl:component=filepicker]',
  `attachfiles` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '附件 [cdl:component=filespicker]',
  `keywords` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '关键字 [cdl:component=textarea] [cdl:search=like]',
  `description` TEXT NULL COMMENT '描述 [cdl:component=editor]',
  `city` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '省市 [cdl:component=citypicker]',
  `mapgis` geometry NULL COMMENT '地理位置经纬度 [cdl:component=mappicker]',
  `json` VARCHAR(255) DEFAULT NULL COMMENT '配置 [cdl:component=jsontable][key=名称,value=值,remark=描述,url=超链接,image=图片]',
  `price` FLOAT(5, 2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '价格 [cdl:component=numberbox] [cdl:search=between]',
  `subtotal` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '小计金额',
  `clickcount` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '点击次数 [cdl:component=number] [cdl:search==,>,<,>=,<=]',
  `rate` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '评分 [cdl:component=rate-picker]',
  `startdate` DATE DEFAULT NULL COMMENT '开始日期 [cdl:component=date]',
  `activitytime` DATETIME DEFAULT NULL COMMENT '活动时间 [cdl:component=datetime]',
  `year` YEAR(4) DEFAULT NULL COMMENT '年 [cdl:component=year]',
  `times` TIME DEFAULT NULL COMMENT '时间 [cdl:component=time]',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  `updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
  `sort` INT(10) NOT NULL DEFAULT 0 COMMENT '排序权重 [cdl:component=sortable]',
  `switch` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '开关 [cdl:component=switch]',
  `status` TINYINT NOT NULL DEFAULT 0 COMMENT '状态(0=隐藏,1=显示)',
  `stage` ENUM('0', '1', '2') NOT NULL DEFAULT '1' COMMENT '阶段:0=未开始,1=第一步,2=第二步',
  `volume` TINYINT NOT NULL DEFAULT 0 COMMENT '音量 [cdl:component=range]',
  `code` TEXT NULL COMMENT '代码 [cdl:component=code]',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='演示表';

渐进式可扩展数据库模型数据表

参考资料:

[数据库字段备注信息声明语法 CDL (Comment Declaration Language) - V2.0] https://www.cnblogs.com/sochishun/p/14132178.html
[数据库设计规范] https://www.cnblogs.com/sochishun/p/14109946.html
[利于复用资料的项目文件命名规范] https://www.cnblogs.com/sochishun/p/14510842.html
[电子商务(电销)平台中订单模块(Order)数据库设计明细] https://www.cnblogs.com/sochishun/p/7040628.html
[电子商务(电销)平台中用户模块(User)数据库设计明细] https://www.cnblogs.com/sochishun/p/7049771.html
[电子商务(电销)平台中内容模块(Content)数据库设计明细] https://www.cnblogs.com/sochishun/p/7060826.html
[电子商务(电销)平台中财务模块(Finance)数据库设计明细] https://www.cnblogs.com/sochishun/p/7060808.html
[电子商务(电销)平台中商品模块(Product)数据库设计明细] https://www.cnblogs.com/sochishun/p/7055490.html
[电子商务(电销)平台中系统设置模块(SysSetting)数据库设计明细] https://www.cnblogs.com/sochishun/p/7050239.html

数据表结构SQL脚本 - 基础模型:

文件:pedm.sql

/**
* 渐进式可扩展数据库模型-基础表-V2
* Progressive Extensible Database Model, pedm
* 版本:2.0.0
* CDL 2.0 语法说明:(建议 CDL 声明写在注释内容的最后面,每个 CDL 声明之间以一个空格隔开,这样排版才不会不影响注释内容的阅读)
*    [cdl:json={"disable":true}] 禁用,当前不可用。
*    [cdl:json={"component":"checkbox"}] 控件类型,详见附1。
*    [cdl:json={"searchable":true,"operate":"like,="}] 是否允许搜索,支持操作符:=,>=,<=,like,null,not_null。默认根据字段数据类型使用贪婪模式。
*    [cdl:json={"display":"index"}] 显示范围:list=列表,create=新建表单,edit=编辑表单,all=全部,none=无,默认 all。
*    [cdl:json={"required":true}] 必填字段
* CDL 支持的控件类型: 
*   // H5 原生组件
*   color, date, datetime-local, month, week, time, email, file, hidden, image, number, password,  range, search, text, url
*   // 业务常用组件
*   radio, checkbox, select, selectpage, selectmulti, passwordbox, bankcardno, textarea, editor, jsontable, numberbox, 
*   image-uploader, images-uploader, file-uploader, files-uploader, city-picker, map-picker, rate-picker, sortable, switch, code
*/

-- DROP DATABASE IF EXISTS db_pedm_v2;
-- CREATE DATABASE IF NOT EXISTS db_pedm_v2 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
-- use db_pedm_v2;

-- SET NAMES utf8mb4;
-- SET FOREIGN_KEY_CHECKS = 0;

-- =============================================================================
--                                  权限类 auth
-- =============================================================================

-- ----------------------------
-- 管理员表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_auth_admin`;
CREATE TABLE `pedm_auth_admin` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_name` VARCHAR(60) NOT NULL COMMENT '用户名',
  `nick_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '昵称',
  `contact_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '姓名',
  `group_name` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '分组名称',
  `password` VARCHAR(32) NOT NULL COMMENT '密码',
  `salt` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '密码盐',
  `avatar` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '头像',
  `email` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '电子邮箱',
  `mobile` VARCHAR(11) NOT NULL DEFAULT '' COMMENT '手机号码',
  `gender` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '性别(0=女,1=男,2=保密)',
  `login_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '登录次数',
  `login_time` DATETIME COMMENT '登录时间',
  `login_ip` VARCHAR(46) NOT NULL DEFAULT '' COMMENT '登录IP',
  `register_ip` VARCHAR(46) NOT NULL COMMENT '注册IP',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  `updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=无效,1=有效)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_name` (`user_name`),
  KEY `idx_email` (`email`),
  KEY `idx_mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员表';

-- ----------------------------
-- 权限分组表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_auth_group`;
CREATE TABLE `pedm_auth_group`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `group_name` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '分组名称',
  `group_title` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '分组标题',
  `rules` VARCHAR(2048) NOT NULL DEFAULT '' COMMENT '规则ID',
  `app_flag` VARCHAR(16) NOT NULL DEFAULT 'admin' COMMENT '模块标志',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_group_name` (`group_name`,`app_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限分组表';

-- ----------------------------
-- 权限规则表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_auth_rule`;
CREATE TABLE `pedm_auth_rule` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `pid` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父级ID',
  `rule_title` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '规则标题',
  `rule_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '规则名称',
  `url` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '规则路径',
  `icon` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '图标',
  `is_menu` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否菜单(0=否,1=是)',
  `extra` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '扩展数据',
  `sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
  `app_flag` VARCHAR(16) NOT NULL DEFAULT 'admin' COMMENT '模块标志',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=隐藏,1=显示)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rule_name` (`rule_name`,`app_flag`),
  KEY `idx_pid` (`pid`),
  KEY `idx_sort` (`sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限规则表';

-- ----------------------------
-- 管理员日志表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_auth_admin_log`;
CREATE TABLE `pedm_auth_admin_log` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '用户名',
  `url` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '操作页面',
  `title` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '日志标题',
  `content` VARCHAR(255) NOT NULL COMMENT '操作内容',
  `ip` VARCHAR(46) NOT NULL DEFAULT '' COMMENT 'IP',
  `user_agent` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '浏览器User-Agent',
  `app_flag` VARCHAR(16) NOT NULL DEFAULT 'admin' COMMENT '模块标志',
  `created_at` INT UNSIGNED NOT NULL COMMENT '操作时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_name` (`user_name`),
  KEY `idx_app_flag`(`app_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员日志表';

-- =============================================================================
--                                  系统类 sys
-- =============================================================================

-- ----------------------------
-- 系统配置表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_config`;
CREATE TABLE `pedm_sys_config` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `group_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '分组名称', -- 这是行内注释
  `conf_name` VARCHAR(60) NOT NULL COMMENT '变量名',
  `conf_title` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '变量标题 -- 这是注释',
  `conf_tip` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '变量描述',
  `conf_type` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '类型(string,TEXT,int,bool,array,datetime,date,file)',
  `conf_value` TEXT NOT NULL COMMENT '变量值',
  `conf_content` TEXT NOT NULL COMMENT '变量字典数据',
  `conf_rule` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '验证规则',
  `conf_extend` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '扩展属性',
  `sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_conf_name` (`conf_name`),
  KEY `idx_sort` (`sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';

-- ----------------------------
-- 附件表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_attachment`;
CREATE TABLE `pedm_sys_attachment` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `file_name` varchar(150) NOT NULL DEFAULT '' COMMENT '文件名',
  `url` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '网址路径',
  `path` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '物理路径',
  `storage` VARCHAR(100) NOT NULL DEFAULT 'local' COMMENT '存储位置',
  `image_width` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '宽度',
  `image_height` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '高度',
  `image_type` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '图片类型',
  `image_frames` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '图片帧数',
  `file_size` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '文件大小',
  `mime_type` VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'mime类型',
  `ext_param` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '透传数据',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  `updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='附件表';

-- ----------------------------
-- 定时任务表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_crontab`;
CREATE TABLE IF NOT EXISTS `pedm_sys_crontab` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `crontab_type` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '任务类型(url=请求URL,sql=执行SQL,shell=执行Shell)',
  `crontab_title` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '任务标题',
  `crontab_content` TEXT COMMENT '任务内容(url,sql,shell)',
  `crontab_schedule` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '执行周期(Crontab格式:min* hour* day* month* weekday*)',
  `max_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '最大执行次数(0为不限)',
  `exec_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '已经执行的次数',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  `updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
  `exec_time` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '最后执行时间',
  `begin_time` DATETIME NOT NULL COMMENT '开始时间',
  `end_time` DATETIME NOT NULL COMMENT '结束时间',
  `sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=禁用,1=启用,2=过期,3=完成)',
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='定时任务表';

-- ----------------------------
-- 定时任务日志表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_crontab_log`;
CREATE TABLE IF NOT EXISTS `pedm_sys_crontab_log` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `crontab_id` INT UNSIGNED NOT NULL COMMENT '任务ID',
  `exec_time` DATETIME COMMENT '执行时间',
  `end_time` DATETIME COMMENT '结束时间',
  `result` TEXT COMMENT '执行结果',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=失败,1=成功)',
  PRIMARY KEY (`id`),
  KEY `idx_crontab_id` (`crontab_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='定时任务日志表';

-- ----------------------------
-- 邮箱验证码表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_email_captcha`;
CREATE TABLE `pedm_sys_email_captcha`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `event` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '事件',
  `email` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '邮箱',
  `code` VARCHAR(6) NOT NULL DEFAULT '' COMMENT '验证码',
  `count` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '验证次数',
  `ip` VARCHAR(46) NOT NULL DEFAULT '' COMMENT 'IP',
  `created_at` INT UNSIGNED NULL DEFAULT 0 COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_email`(`email`,`event`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='邮箱验证码表';

-- ----------------------------
-- 短信验证码表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_sms_captcha`;
CREATE TABLE `pedm_sys_sms_captcha` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `event` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '事件',
  `mobile` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
  `code` VARCHAR(6) NOT NULL DEFAULT '' COMMENT '验证码',
  `count` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '验证次数',
  `ip` VARCHAR(46) NOT NULL DEFAULT '' COMMENT 'IP',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_mobile`(`mobile`,`event`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='短信验证码表';

-- ----------------------------
-- 用户反馈表
-- ----------------------------
DROP TABLE IF EXISTS `pedm_sys_feedback`;
CREATE TABLE IF NOT EXISTS `pedm_sys_feedback` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `user_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户名',
  `contact_name` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '姓名',
  `mobile` VARCHAR(11) NOT NULL DEFAULT '' COMMENT '手机号',
  `email` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '电子邮箱',
  `category` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '反馈类别',
  `content` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '反馈内容',
  `images` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '截图',
  `admin_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '管理员用户名',
  `reply_content` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '回复内容',
  `is_read` TINYINT NOT NULL DEFAULT 0 COMMENT '是否已阅(0=否,1=是)',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=正常)',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  `updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '回复时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户反馈表';

数据表结构SQL脚本 - 业务扩展模型:

文件:biz.sql

/**
* 渐进式可扩展数据库模型-业务扩展模型-V2
* Progressive Extensible Database Model, pedm
* 版本:2.0.0
* CDL 2.0 语法说明:(建议 CDL 声明写在注释内容的最后面,每个 CDL 声明之间以一个空格隔开,这样排版才不会不影响注释内容的阅读)
*    [cdl:json={"disable":true}] 禁用,当前不可用。
*    [cdl:json={"component":"checkbox"}] 控件类型,详见附1。
*    [cdl:json={"searchable":true,"operate":"like,="}] 是否允许搜索,支持操作符:=,>=,<=,like,null,not_null。默认根据字段数据类型使用贪婪模式。
*    [cdl:json={"display":"index"}] 显示范围:list=列表,create=新建表单,edit=编辑表单,all=全部,none=无,默认 all。
*    [cdl:json={"required":true}] 必填字段
* CDL 支持的控件类型: 
*   // H5 原生组件
*   color, date, datetime-local, month, week, time, email, file, hidden, image, number, password,  range, search, text, url
*   // 业务常用组件
*   radio, checkbox, select, selectpage, selectmulti, passwordbox, bankcardno, textarea, editor, jsontable, numberbox, 
*   image-uploader, images-uploader, file-uploader, files-uploader, city-picker, map-picker, rate-picker, sortable, switch, code
*/

-- =============================================================================
--                                  文章内容类 art
-- =============================================================================

-- ----------------------------
-- 文章表
-- ----------------------------
DROP TABLE IF EXISTS `art_article`;
CREATE TABLE `art_article`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',  
  `category_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分类ID [cdl:component=select(table=ps_article_category,textfield=category_name,idfield=id)]',
  `category_name` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '分类名称',
  `title` VARCHAR(255) NOT NULL COMMENT '文章标题',
  `author` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '文章作者',
  `source_link` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '原文链接 [cdl:component=url]',
  `icon` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '文章图标 [cdl:component=image]',
  `summary` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '文章摘要 [cdl:component=textarea]',
  `share_title` VARCHAR(180) NOT NULL DEFAULT '' COMMENT '文章分享标题 [cdl:disable]',
  `share_intro` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '文章分享简介 [cdl:component=textarea] [cdl:disable]',
  `stat_visit_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '浏览次数 [cdl:search=search(like,=)] [cdl:display=index]',
  `is_hot` TINYINT NOT NULL DEFAULT 0 COMMENT '是否热门(0=否,1=是)',
  `is_recommend` TINYINT NOT NULL DEFAULT 0 COMMENT '是否推荐(0=否,1=是)',
  `sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  `content` TEXT NOT NULL COMMENT '文章内容 [cdl:component=editor]',
  PRIMARY KEY (`id`),
  KEY `idx_title` (`title`),
  KEY `idx_category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

-- ----------------------------
-- 文章分类表
-- ----------------------------
DROP TABLE IF EXISTS `art_article_category`;
CREATE TABLE `art_article_category`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `pid` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父级ID [cdl:component=select(table=ps_article_category,textfield=category_name,idfield=id)]',
  `category_name` VARCHAR(30) NOT NULL COMMENT '标题',
  `category_code` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '代码 [cdl:disable]',  
  `intro` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '简介 [cdl:component=textarea]',
  `seo_keywords` VARCHAR(150) NOT NULL DEFAULT '' COMMENT 'SEO 关键词 [cdl:disable]',
  `seo_description` VARCHAR(500) NOT NULL DEFAULT '' COMMENT 'SEO 描述 [cdl:disable]',
  `icon` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '图标',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
  `sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_pid` (`pid`),
  KEY `idx_sort`(`sort`),
  KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章分类表';

-- =============================================================================
--                                  电商类 eb
-- =============================================================================

-- ----------------------------
-- 购物车表
-- ----------------------------
DROP TABLE IF EXISTS `eb_shopping_cart`;
CREATE TABLE `eb_shopping_cart`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `product_id` INT UNSIGNED NOT NULL COMMENT '商品ID',
  `product_spec_id` INT NOT NULL DEFAULT 0 COMMENT '商品规格ID',
  `quantity` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品数量',
  `extra` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '扩展数据',
  `flag` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '标识(buynow=立即购买,mobile=手机充值)',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=失效,1=有效)',
  PRIMARY KEY (`id`),
  KEY `idx_user_id`(`user_id`),
  KEY `idx_status`(`user_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购物车表';

-- ----------------------------
--  用户商品收藏表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_favorite`;
CREATE TABLE `eb_product_favorite` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `product_id` INT UNSIGNED NOT NULL COMMENT '商品ID',
  `tag` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '标签(逗号隔开)',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id`(`user_id`),
  KEY `idx_product_id`(`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户商品收藏表';

-- ----------------------------
-- 商品表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_goods`;
CREATE TABLE `eb_product_goods` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `category_id` INT UNSIGNED NOT NULL COMMENT '分类ID [cdl:component=select(table=ps_product_category,textfield=category_name,idfield=id)]',
  `product_name` VARCHAR(150) NOT NULL COMMENT '商品名称',
  `model` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '型号编码 [cdl:disable]',
  `sku` varchar(64) NOT NULL DEFAULT '' COMMENT '商品唯一标识编码 [cdl:disable]',
  `upc` varchar(12) NOT NULL DEFAULT '' COMMENT 'UPC条形码 [cdl:disable]',
  `ean` varchar(14) NOT NULL DEFAULT '' COMMENT 'EAN条形码 [cdl:disable]',
  `isbn` VARCHAR(17) NOT NULL DEFAULT '' COMMENT '国际标准书号 [cdl:disable]',
  `tag` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '商品标签(逗号隔开) [cdl:disable]',
  `icon` VARCHAR(255) NOT NULL COMMENT '图标',
  `carousel_images` TEXT NOT NULL COMMENT '轮播图  [cdl:display=create,edit]',
  `video_url` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '短视频 [cdl:disable]',
  `intro` VARCHAR(255) NOT NULL COMMENT '商品简介 [cdl:component=textarea] [cdl:display=create,edit]',
  `seo_keywords` VARCHAR(96) NOT NULL DEFAULT '' COMMENT 'SEO 关键词 [cdl:disable]',
  `price` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '商品价格',
  `market_price` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '市场价',
  `original_price` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '原价 [cdl:disable]',
  `cost_price` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '成本价 [cdl:disable]',
  `vip_price` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '贵宾价格 [cdl:disable]',
  `promotion_price` DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '促销价格',
  `promotion_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '促销类型(0=无促销,1=抢购,2=限时折扣,3=秒杀)',
  `give_integral` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '赠送积分',
  `postage_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '邮费(0=免运费)',
  `is_issue_invoice` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否开具增值税发票(0=否,1=是) [cdl:disable]',
  `unit_name` VARCHAR(15) NOT NULL DEFAULT '' COMMENT '库存单位 [cdl:disable]',
  `stat_stock_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '总库存',
  `stat_sales_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '销量 [cdl:display=index]',
  `stat_visited_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '访问量 [cdl:display=index]',
  `stat_favorite_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '收藏量 [cdl:display=index]',
  `stat_star_quantity` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '好评星级 [cdl:disable]',
  `stat_evaluation_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '评价数 [cdl:disable]',
  `is_limited_quantity` TINYINT NOT NULL DEFAULT 0 COMMENT '是否限量销售(0=否,1=是)',
  `is_limited_time` TINYINT NOT NULL DEFAULT 0 COMMENT '是否限时销售(0=否,1=是)',
  `limit_quantity` INT NOT NULL DEFAULT 0 COMMENT '销售限量',
  `begin_time` DATETIME COMMENT '开始时间 [cdl:disable]',
  `end_time` DATETIME COMMENT '结束时间 [cdl:disable]',
  `is_virtual` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否为虚拟商品(0=否,1=是) [cdl:disable]',
  `is_deduction_inventory` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否扣减库存(0=否,1=是) [cdl:disable]',
  `is_appoint` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否是预约商品(0=否,1=是) [cdl:disable]',
  `is_presell` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否是预售商品(0=否,1=是) [cdl:disable]',
  `is_have_gift` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否拥有赠品(0=否,1=是) [cdl:disable]',
  `is_hot` TINYINT NOT NULL DEFAULT 0 COMMENT '是否热销(0=否,1=是)',
  `is_recommend` TINYINT NOT NULL DEFAULT 0 COMMENT '是否推荐(0=否,1=是)',
  `is_new` TINYINT NOT NULL DEFAULT 0 COMMENT '是否新品(0=否,1=是)',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=未上架,1=上架)',
  `created_at` INT UNSIGNED DEFAULT NULL COMMENT '创建时间',
  `updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '更新时间',
  `sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重 [cdl:disable]',
  `soure_link` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '外部链接(淘宝、天猫、1688商品保存标识,避免商品重复入库) [cdl:component=url]',
  `brand_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '品牌ID [cdl:disable]',
  `mch_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商户ID(0=管理员,n=商户) [cdl:disable]',
  `content` TEXT NOT NULL COMMENT '商品描述(PC端) [cdl:component=editor]',
  `content_m` TEXT NOT NULL COMMENT '商品描述(移动端) [cdl:component=editor]',
  PRIMARY KEY (`id`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_product_name` (`product_name`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- ----------------------------
-- 商品分类表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_category`;
CREATE TABLE `eb_product_category`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `pid` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父级ID [cdl:component=select(table=ps_product_category,textfield=category_name,idfield=id)]',
  `category_name` VARCHAR(60) NOT NULL COMMENT '分类名称',
  `subtitle` VARCHAR(80) NOT NULL DEFAULT '' COMMENT '副标题 [cdl:disable]',
  `is_allow_virtual` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否允许发布虚拟商品(0=否,1=是) [cdl:disable]',
  `seo_keywords` VARCHAR(150) NOT NULL DEFAULT '' COMMENT 'SEO 关键词 [cdl:disable]',
  `seo_description` VARCHAR(500) NOT NULL DEFAULT '' COMMENT 'SEO 描述 [cdl:disable]',
  `icon` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '图标',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
  `sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_pid`(`pid`),
  KEY `idx_status` (`status`),
  KEY `idx_sort`(`sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表';

-- ----------------------------
-- 商品评论表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_comments`;
CREATE TABLE `eb_product_comments`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `product_id` INT UNSIGNED NOT NULL COMMENT '商品ID',
  `order_id` INT UNSIGNED NOT NULL COMMENT '订单ID',
  `rating` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '评价级别',
  `product_score` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品分数',
  `service_score` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '服务分数',
  `content` VARCHAR(200) NOT NULL COMMENT '评论内容',
  `images` TEXT COMMENT '图片',
  `created_at` INT UNSIGNED NOT NULL COMMENT '评论时间',
  `reply_time` DATETIME COMMENT '管理员回复时间',
  `reply_content` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '管理员回复内容',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=隐藏,1=显示)',
  `is_anonymous` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否匿名(0=否,1=是)',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品评论表';

-- ----------------------------
--  商品分类规格表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_specification`;
CREATE TABLE `eb_product_specification` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `spec_name` VARCHAR(60) NOT NULL COMMENT '规格名称',
  `spec_code` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '规格代码',
  `category_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品分类ID',
  `category_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '商品分类名称',
  `sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
  PRIMARY KEY (`id`),
  KEY `idx_category_id`(`category_id`),
  KEY `idx_sort`(`sort`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='商品分类规格表';

-- ----------------------------
--  商品分类规格值表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_specification_value`;
CREATE TABLE `eb_product_specification_value` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `spec_id` INT UNSIGNED NOT NULL COMMENT '所属规格ID',
  `category_id` INT UNSIGNED NOT NULL COMMENT '分类ID',
  `spec_value_name` VARCHAR(60) NOT NULL COMMENT '规格值名称',
  `spec_value_color` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '规格颜色',
  `sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
  PRIMARY KEY (`id`),
  KEY `idx_spec_id`(`spec_id`),
  KEY `idx_sort`(`sort`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='商品分类规格值表';

-- ----------------------------
--  商品规格信息表
-- ----------------------------
DROP TABLE IF EXISTS `eb_product_specification_info`;
CREATE TABLE `eb_product_specification_info` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `product_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品ID',
  `spec_value_id` INT UNSIGNED NOT NULL COMMENT '所属规格ID',
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '销售价格',
  `cost` DECIMAL(8,2) UNSIGNED NOT NULL COMMENT '成本价',
  `stock` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存',
  `sales` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '销量',
  `image` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '图片',
  `weight` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '重量(kg) ',
  `barcode` VARCHAR(80) NOT NULL DEFAULT '' COMMENT '条形码',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_spec_value_id` (`spec_value_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品规格信息表';

-- ----------------------------
-- 优惠券表
-- ----------------------------
DROP TABLE IF EXISTS `eb_coupon`;
CREATE TABLE `eb_coupon`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `coupon_name` VARCHAR(60) NOT NULL COMMENT '优惠券名称',
  `coupon_code` VARCHAR(32) NOT NULL COMMENT '优惠券代码',
  `integral` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '兑换消耗积分值',
  `coupon_value` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '优惠券面值',
  `use_min_amount` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '最低消费多少金额可使用用优惠券',
  `expiry_time` DATETIME COMMENT '优惠券有效期',
  `begin_time` DATETIME COMMENT '开始领取时间',
  `end_time` DATETIME COMMENT '结束领取时间',
  `total_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '优惠券总数量',
  `quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '优惠券可领取数量',
  `is_unlimited` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否无限张数(0=否,1=是)',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=禁用,1=启用,2=过期,3=领完)',
  `sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_coupon_code`(`coupon_code`),
  KEY `idx_time_range`(`begin_time`, `end_time`),
  KEY `idx_quantity`(`quantity`),
  KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券表';

-- ----------------------------
-- 优惠券领取表
-- ----------------------------
DROP TABLE IF EXISTS `eb_coupon_receive`;
CREATE TABLE `eb_coupon_receive`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `coupon_id` INT UNSIGNED NOT NULL COMMENT '优惠券ID',
  `coupon_title` VARCHAR(60) NOT NULL COMMENT '优惠券名称',
  `coupon_value` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '优惠券面值',
  `use_min_amount` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '最低消费多少金额可用优惠券',
  `created_at` INT UNSIGNED NOT NULL COMMENT '领取时间',
  `expiry_time` DATETIME COMMENT '优惠券有效期',
  `use_time` DATETIME COMMENT '使用时间',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=未使用,1=已使用,2=已过期)',
  PRIMARY KEY (`id`),
  KEY `idx_coupon_id`(`coupon_id`),
  KEY `idx_user_id`(`user_id`),
  KEY `idx_expiry_time`(`expiry_time`),
  KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='优惠券领取表';

-- ----------------------------
-- 快递公司表
-- ----------------------------
DROP TABLE IF EXISTS `eb_express`;
CREATE TABLE `eb_express` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `express_name` VARCHAR(30) NOT NULL COMMENT '快递公司名称',
  `express_code` VARCHAR(50) NOT NULL COMMENT '快递公司代码',
  `homepage` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '快递公司网址 [cdl:required]',
  `icon` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '图标',
  `sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
  `api_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '接口API状态(0=不可用,1=可用)',
  `api_url` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '接口API地址',
  `api_key` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '接口API参数签名密钥',
  `api_additional_param` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '接口API附加参数(URL请求参数格式,例如:id=1,2,3&name=a,b,c)',
  `api_method` CHAR(4) NOT NULL DEFAULT 'POST' COMMENT '接口API的请求方法(GET=GET请求,POST=POST请求)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`express_code`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='快递公司表';

-- ----------------------------
-- 订单表
-- ----------------------------
DROP TABLE IF EXISTS `eb_order`;
CREATE TABLE `eb_order`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `order_no` VARCHAR(32) NOT NULL COMMENT '订单编号',
  `order_from` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单来源(1=PC,2=手机)',
  `total_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单商品总数',
  `total_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '订单总价',
  `postage_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '邮费',
  `pay_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '实际支付金额',
  `pay_postage_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '支付邮费',
  `deduction_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '抵扣金额',
  `give_integral` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单赠送积分',
  `gain_integral` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '消费赚取积分',
  `use_integral` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '使用积分',
  `coupon_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '优惠券ID',
  `coupon_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '优惠券金额',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  `pay_time` DATETIME COMMENT '支付时间',
  `payment_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '支付方式ID',
  `trade_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '第三方交易单号',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单状态(0=待确认,1=待发货,2=待收货,3=已收货,4=已完成,5=已退款,6=已取消,7=已关闭)',
  `evaluation_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '评价状态(0=未评价,1=已评价,2=已过期未评价)',
  `pay_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '支付状态(0=未支付,1=已支付,2=已退款,3=部分退款)',
  `refund_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '退款状态(0=未退款,1=申请中,2=已退款)',
  `refund_reason_image` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '退款图片',
  `refund_reason_explain` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '退款用户说明',
  `refund_reason_time` DATETIME COMMENT '退款时间',
  `refund_reject_explain` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '不退款的理由',
  `refund_amount` DECIMAL(8,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '退款金额',
  `delivery_name` VARCHAR(60) NOT NULL COMMENT '收货人姓名',
  `delivery_phone` VARCHAR(18) NOT NULL COMMENT '收货人电话',
  `delivery_address` VARCHAR(100) NOT NULL COMMENT '收货人详细地址',
  `express_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '快递公司ID',
  `express_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '快递单号',
  `buyer_remark` VARCHAR(512) NOT NULL COMMENT '买家备注',
  `admin_remark` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '管理员备注',
  `confirm_remark` VARCHAR(512) NOT NULL DEFAULT '' COMMENT '订单确认备注',
  `confirm_time` DATETIME COMMENT '订单确认时间',
  `delivery_time` DATETIME COMMENT '发货时间',
  `cancel_time` DATETIME COMMENT '取消时间',
  `receiving_time` DATETIME COMMENT '收货时间',
  `close_time` DATETIME COMMENT '关闭时间',
  `mch_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商户ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no`(`order_no`),
  KEY `idx_user_id`(`user_id`),
  KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- ----------------------------
-- 订单商品表
-- ----------------------------
DROP TABLE IF EXISTS `eb_order_detail`;
CREATE TABLE `eb_order_detail`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `order_id` INT UNSIGNED NOT NULL COMMENT '订单ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '买家ID',
  `product_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品ID',
  `product_name` VARCHAR(60) NOT NULL COMMENT '商品名称',
  `product_price` DECIMAL(10,2) NOT NULL COMMENT '商品价格',
  `product_quantity` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品数量',
  `product_image` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品图片',
  `tax` DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '税额',
  `subtotal` DECIMAL(10,2) UNSIGNED NOT NULL COMMENT '小计金额',
  `product_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品类型(0=默认,1=抢购商品,2=限时折扣商品,3=组合套装,4=赠品,5=拼团,6=用户等级折扣)',
  `promotions_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '促销活动ID(抢购ID/限时折扣ID/优惠套装ID)与 product_type 搭配使用',
  PRIMARY KEY (`id`),
  KEY `idx_order_id`(`order_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_product_id`(`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单商品表';

-- ----------------------------
--  订单退货表
-- ----------------------------
DROP TABLE IF EXISTS `eb_order_return`;
CREATE TABLE `eb_order_return` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `order_id` INT UNSIGNED NOT NULL COMMENT '订单ID',
  `order_no` VARCHAR(32) NOT NULL COMMENT '订单编号',
  `refund_no` VARCHAR(32) NOT NULL COMMENT '退货编号',
  `user_id` INT UNSIGNED NOT NULL COMMENT '买家ID',
  `user_name` VARCHAR(60) NOT NULL COMMENT '买家用户名',
  `order_detail_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '订单详情id(0=全部退款)',
  `product_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品id(0=全部退款)',
  `product_name` VARCHAR(60) NOT NULL COMMENT '商品名称',
  `product_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品退货数量',
  `refund_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '退款金额',
  `refund_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '申请类型(1=仅退款,2=退货退款)',
  `return_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '退货类型(1=不用退货,2=需要退货)',
  `audit_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '审核状态(1=待审核,2=同意,3=不同意)',
  `delivery_status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '物流状态(1=待发货,2=待收货,3=未收到,4=已收货)',
  `images` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '退款退货凭证图片',
  `buyer_message` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '退款退货申请原因',
  `admin_message` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '管理员备注',
  `express_id` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '物流公司编号',
  `express_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '物流单号',
  `delivery_time` DATETIME COMMENT '发货时间',
  `delay_time` DATETIME COMMENT '收货延迟时间',
  `receive_time` DATETIME COMMENT '收货时间',
  `receive_message` VARCHAR(300) NOT NULL DEFAULT '' COMMENT '收货备注',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  `admin_operate_time` DATETIME COMMENT '管理员处理时间',
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_audit_status` (`audit_status`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='订单退货表';

-- ----------------------------
-- 订单操作日志表
-- ----------------------------
DROP TABLE IF EXISTS `eb_order_log`;
CREATE TABLE `eb_order_log`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `admin_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '操作者用户名',
  `order_id` INT UNSIGNED NOT NULL COMMENT '订单ID',
  `operate` VARCHAR(32) NOT NULL COMMENT '操作名称',
  `message` VARCHAR(255) NOT NULL COMMENT '操作备注',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_order_id`(`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单操作日志表';

-- ----------------------------
-- 发票信息表
-- ----------------------------
DROP TABLE IF EXISTS `eb_invoice`;
CREATE TABLE `eb_invoice` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `order_id` INT UNSIGNED NOT NULL COMMENT '订单ID',
  `invoice_type` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '发票类型(1=普通发票,2=增值税发票)',
  `invoice_title` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '发票抬头(普通发票)',
  `invoice_code` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '纳税人识别号(普通发票)',
  `invoice_content` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '发票内容(普通发票)',
  `invoice_company` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '单位名称',
  `invoice_company_code` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '纳税人识别号',
  `invoice_reg_address` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '注册地址',
  `invoice_reg_phone` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '注册电话',
  `invoice_reg_bank_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '开户银行',
  `invoice_reg_bank_account` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '银行帐户',
  `invoice_delivery_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '收票人姓名',
  `invoice_delivery_phone` VARCHAR(15) NOT NULL DEFAULT '' COMMENT '收票人手机号',
  `invoice_delivery_addrress` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '收票人地址',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_order_id`(`order_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COMMENT='发票信息表';


-- =============================================================================
--                                  用户类 usr
-- =============================================================================

-- ----------------------------
-- 用户表
-- ----------------------------
DROP TABLE IF EXISTS `usr_user`;
CREATE TABLE `usr_user`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_name` VARCHAR(60) NOT NULL COMMENT '用户账号',
  `nick_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '用户昵称',
  `contact_name` VARCHAR(25) NOT NULL DEFAULT  '' COMMENT  '真实姓名',
  `password` VARCHAR(32) NOT NULL COMMENT '用户密码(MD5加密)[cdl:display=create,edit]',
  `salt` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '密码盐 [cdl:display=none]',
  `trade_pwd` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '交易密码 [cdl:display=create]',
  `birthday` DATE COMMENT  '生日 [cdl:display=none]',
  `gender` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '性别(0=女,1=男,2=保密)',
  `idcard_no` VARCHAR(18) NOT NULL DEFAULT  '' COMMENT  '身份证号码 [cdl:disable]',
  `group_name` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '分组名称 [cdl:disable]',
  `level` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户等级',
  `avatar` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '用户头像',
  `email` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '电子邮箱',
  `mobile` VARCHAR(11) NOT NULL DEFAULT '' COMMENT '手机号码',
  `created_at` INT UNSIGNED NOT NULL COMMENT '注册时间 [cdl:display=none]',
  `register_ip` VARCHAR(46) NOT NULL COMMENT '注册IP [cdl:display=none]',
  `login_time` DATETIME COMMENT '上次登录时间',
  `login_ip` VARCHAR(46) NOT NULL DEFAULT '' COMMENT '上次登录IP [cdl:display=none]',
  `stat_total_money` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '累计金额 [cdl:display=index]',
  `blance_money` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '用户余额 [cdl:display=index]',
  `frozen_money` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '用户冻结金额 [cdl:display=index]',
  `integral` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户积分 [cdl:display=index]',
  `experience` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户经验值 [cdl:display=index]',
  `signin_days_total` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '签到总天数 [cdl:display=none]',
  `signin_days_series` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT  '持续签到天数总数(非连续周期清零)',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(1=正常,0=禁止)',
  `remark` VARCHAR(120) NOT NULL DEFAULT  '' COMMENT  '备注信息',
  `inviter_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '邀请人ID [cdl:display=index]',
  `alipay_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '支付宝openID [cdl:display=none]',
  `weixin_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '微信openID [cdl:display=none]',
  `weixin_unionid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '微信unionID [cdl:display=none]',
  `weixin_web_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '微信web用户openID [cdl:display=none]',
  `qq_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT 'QQopenID [cdl:display=none]',
  `qq_unionid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT 'QQunionID [cdl:display=none]',
  `baidu_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '百度openID [cdl:display=none]',
  `toutiao_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '今日头条openID [cdl:display=none]',
  `sina_openid` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '新浪微博openID [cdl:display=none]',
  PRIMARY KEY (`id`),
  KEY `idx_user_name`(`user_name`),
  KEY `idx_mobile` (`mobile`),
  KEY `idx_email` (`email`),
  KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- ----------------------------
-- 用户分组表
-- ----------------------------
DROP TABLE IF EXISTS `usr_group`;
CREATE TABLE `usr_group`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `group_name` VARCHAR(30) NOT NULL COMMENT '分组名称',
  `group_title` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '分组标题',
  `rules` VARCHAR(2048) NOT NULL DEFAULT '' COMMENT '规则ID',
  `sort` SMALLINT UNSIGNED NOT NULL DEFAULT 999 COMMENT '排序权重',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
  PRIMARY KEY (`id`),
  KEY `idx_group_name`(`group_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户分组表';

-- ----------------------------
-- 用户等级表
-- ----------------------------
DROP TABLE IF EXISTS `usr_level`;
CREATE TABLE `usr_level`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `level_value` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '等级值',
  `level_name` VARCHAR(30) NOT NULL COMMENT '等级名称',
  `level_title` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '等级标题',
  `icon` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '图标',
  `experience` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '所需经验值',
  `extra` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '扩展数据',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=隐藏,1=显示)',
  PRIMARY KEY (`id`),
  KEY `idx_level_value`(`level_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户等级表';

-- ----------------------------
-- 推广员表
-- ----------------------------
DROP TABLE IF EXISTS `usr_promoter`;
CREATE TABLE IF NOT EXISTS `usr_promoter` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=审核中,1=已审核,2=已清退)',
  `stat_total_amount` DECIMAL(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '累计佣金 ',
  `stat_lv1_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '1级成员数量',
  `stat_lv2_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '2级成员数量',
  `stat_lv3_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '3级成员数量',
  `created_at` INT UNSIGNED NOT NULL COMMENT '申请时间',
  `stat_product_quantity` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '分销商品累计件数',
  `stat_product_amount` DECIMAL(10,0) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '分销商品累计金额',
  PRIMARY KEY (`id`),
  KEY `idx_user_id`(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='推广员表';

-- ----------------------------
-- 用户收货地址表
-- ----------------------------
DROP TABLE IF EXISTS `usr_address`;
CREATE TABLE `usr_address`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `contact_name` VARCHAR(60) NOT NULL COMMENT '姓名',
  `mobile` VARCHAR(11) NOT NULL COMMENT '手机号',
  `province` VARCHAR(60) NOT NULL COMMENT '省份',
  `city` VARCHAR(60) NOT NULL COMMENT '城市',
  `street` VARCHAR(60) NOT NULL COMMENT '街道',
  `detail` VARCHAR(150) NOT NULL COMMENT '详细地址',
  `post_code` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '邮编',
  `longitude` DOUBLE(10,6) NOT NULL DEFAULT 0 COMMENT '经度(分米级)',
  `latitude` DOUBLE(10,6) NOT NULL DEFAULT 0 COMMENT '纬度(分米级)',
  `is_default` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否默认(0=否,1=是)',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id`(`user_id`),
  KEY `idx_is_default`(`is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户收货地址表';

-- ----------------------------
-- 用户积分日志表
-- ----------------------------
DROP TABLE IF EXISTS `usr_integral_log`;
CREATE TABLE `usr_integral_log` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `user_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户名',
  `integral` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '变更积分',
  `memo` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '变更原因',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id`(`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户积分日志表';


-- =============================================================================
--                                  财务类 fin
-- =============================================================================

-- ----------------------------
-- 财务提现表
-- ----------------------------
DROP TABLE IF EXISTS `fin_withdrawal`;
CREATE TABLE `fin_withdrawal`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `serial_no` VARCHAR(16) NOT NULL COMMENT '流水号',
  `contact_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '姓名',
  `payment_type` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '支付方式(bank=银行卡,alipay=支付宝,weixin=微信)',
  `trade_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '第三方交易单号',
  `bank_name` VARCHAR(60) NOT NULL COMMENT '银行名称',
  `bank_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '银行卡号',
  `bank_holder` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '开户人姓名',
  `alipay_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '支付宝账号',
  `wechat_no` VARCHAR(15) NOT NULL DEFAULT '' COMMENT '微信号',
  `amount` DECIMAL(10,2) UNSIGNED NULL DEFAULT 0.00 COMMENT '提现金额',
  `commission_amount` DECIMAL(6,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '手续费',
  `user_remark` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '用户备注',
  `admin_remark` VARCHAR(150) NOT NULL DEFAULT '' COMMENT '管理员备注',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  `updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '审核时间',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=审核中,1=已提现,2=未通过)',
  PRIMARY KEY (`id`),
  KEY `idx_user_id`(`user_id`),
  KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户提现表';

-- ----------------------------
-- 财务充值表
-- ----------------------------
DROP TABLE IF EXISTS `fin_recharge`;
CREATE TABLE `fin_recharge`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `serial_no` VARCHAR(16) NOT NULL COMMENT '流水号',
  `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '充值金额',
  `user_remark` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '用户备注',
  `admin_remark` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '管理员备注',
  `payment_type` VARCHAR(16) NOT NULL DEFAULT '' COMMENT '支付方式(cash=现金,bank=银行转账,alipay=支付宝,weixin=微信)',
  `trade_no` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '第三方交易单号',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态(0=未支付,1=已支付,2=已取消)',
  `created_at` INT UNSIGNED NOT NULL COMMENT '充值时间',
  `updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '支付时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id`(`user_id`),
  KEY `idx_status`(`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户充值表';

-- ----------------------------
-- 财务日志表
-- ----------------------------
DROP TABLE IF EXISTS `fin_log`;
CREATE TABLE IF NOT EXISTS `fin_log` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `memo` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '变更原因',
  `user_id` INT NOT NULL COMMENT '用户ID [cdl:display=none]',
  `user_name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户名',
  `currency_type` TINYINT NOT NULL DEFAULT 1 COMMENT '货币类型(1=人民币,2=虚拟币)',
  `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '变更金额',
  `item_id` INT NOT NULL DEFAULT 0 COMMENT '品项ID',
  `item_name` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '品项名称',
  `item_event` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '交易事件',
  `status` TINYINT NOT NULL COMMENT '资金流向(1=充值成功,2=提现申请,3=提现成功,4=提现撤回,11=商城下单,12=商城退货,13=取消订单,61=兑换礼包,62=转账扣款,63=转账收款)',
  `created_at` INT UNSIGNED NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_currency_type` (`currency_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户财务日志表';


-- =============================================================================
--                                  统计类 stat
-- =============================================================================

-- ----------------------------
-- 用户浏览商品统计分析表
-- ----------------------------
DROP TABLE IF EXISTS `stat_user_product_visit`;
CREATE TABLE `stat_user_product_visit`  (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` INT UNSIGNED NOT NULL COMMENT '用户ID',
  `product_id` INT UNSIGNED NOT NULL COMMENT '商品ID',
  `category_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品分类ID',
  `created_at` INT UNSIGNED NOT NULL COMMENT '添加时间',
  `date_ymd` DATE NOT NULL DEFAULT '1970-1-1' COMMENT '日期',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户浏览商品统计分析表';

-- =============================================================================
--                                  其他类
-- =============================================================================

-- ----------------------------
-- 代理商表
-- ----------------------------
DROP TABLE IF EXISTS `agt_agent_admin`;
CREATE TABLE IF NOT EXISTS `agt_agent_admin` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_name` VARCHAR(60) NOT NULL COMMENT '用户名',
  `nick_name` VARCHAR(60) NOT NULL DEFAULT '' COMMENT '昵称',
  `contact` VARCHAR(30) NOT NULL DEFAULT '' COMMENT '联系人',
  `wechat` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '微信',
  `password` VARCHAR(32) NOT NULL COMMENT '密码 [cdl:display=create,edit]',
  `salt` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '密码盐 [cdl:display=none]',
  `avatar` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '头像',
  `email` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '电子邮箱',
  `mobile` VARCHAR(11) NOT NULL DEFAULT '' COMMENT '手机号码',
  `gender` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '性别(0=女,1=男,2=保密)',
  `login_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '登录次数',
  `login_time` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '登录时间 [cdl:display=index]',
  `login_ip` VARCHAR(46) NOT NULL DEFAULT '' COMMENT '登录IP [cdl:display=none]',
  `register_ip` VARCHAR(46) NOT NULL COMMENT '注册IP [cdl:display=none]',
  `created_at` INT UNSIGNED NOT NULL COMMENT '注册时间 [cdl:display=none]',
  `updated_at` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '审核时间 [cdl:display=none]',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态(0=待审核,1=审核通过,2=审核失败,3=禁用)',
  `audit_comment` VARCHAR(200) NOT NULL DEFAULT '' COMMENT '审核备注',
  `commission_rate` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '提成比率',
  `total_amount` INT NOT NULL DEFAULT 0 COMMENT '累计金额 [cdl:display=index]',
  `blance_amount` INT NOT NULL DEFAULT 0 COMMENT '可用余额 [cdl:display=index]',
  `frozen_amount` INT NOT NULL DEFAULT 0 COMMENT '冻结金额 [cdl:display=index]',
  `stat_withdrawal_amount` INT NOT NULL DEFAULT 0 COMMENT '提现总金额 [cdl:display=none]',
  `stat_income_amount` INT NOT NULL DEFAULT 0 COMMENT '收益总金额 [cdl:display=none]',
  `stat_referral_quantity` INT NOT NULL DEFAULT 0 COMMENT '推广量 [cdl:display=none]',
  PRIMARY KEY (`id`),
  KEY `idx_user_name` (`user_name`),
  KEY `idx_mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='代理商表';
posted on 2020-12-09 17:37  sochishun  阅读(260)  评论(0编辑  收藏  举报