MySQL-场景篇-新零售数据结构设计

holic-x2024年6月24日...大约 38 分钟

MySQL-场景篇-新零售数据结构设计

学习核心

针对零售场景的数据结构设计开展MySQL设计学习
- 拆解苏宁易购数据结构设计思想，在理解业务需求的基础上学习数据结构设计

学习资料

新零售数据结构设计open in new window

概念梳理

产品和商品

理解产品和商品概念的区分，以发布会为例，发布会上发布了iphoneX，讲解的是其共性信息，这是发布产品概念，具体到128G黑色iphoneX为一个具体的商品信息。因此针对上述概念拆分设计为产品表和商品表

SPU

SPU 全称 Standard Product Unit，是标准产品单位。SPU 描述一个产品的各种特性
- 例如：ThinkPad 产品（类似的还有小米9、iphoneX等）
  - 有各种销售方案，描述一些共性信息，归属于产品表，一个产品下可包含多个商品信息
什么情况下会用到SPU？
- 例如电商平台检索商品信息的时候（例如关键字搜索ipad，获取到系统返回的商品列表信息）
  - 根据搜索关键字检索产品表的关联产品主键ID
  - 随后根据产品主键ID检索商品信息（按照一定的规则将商品信息返回，例如销量最高或者最优推荐等检索条件）
- 为什么不直接搜索商品表中的记录
  - 因为一个产品中可能会有多个商品（产品表的记录数量远远少于商品表），通过这种方式关联查找可以有效提升检索性能

SKU

SKU 全称 Stock Keeping Unit ，库存进出计量的单位，SKU 是物理上不可分割的最小存货单元
- 例如一个商品（以某个手机信息为例）：商品的属性由网络类型、机身颜色、套餐类型、存储容量组成
【库存】字段是否应该存储于SKU表中？
- 结合实际业务场景区分，例如一个大型连锁店中每个店铺的库存都不同，所以要结合实际业务场景来选择其定义在哪个表中

SKU与权重

B2B 电商平台：类似淘宝这样的电商平台。企业开店，卖东西给客户；

B2C 电商平台：类似苏宁易购这样的平台，自己搭建平台，将商品卖给客户；

京东是基于B2B、B2C的混合模式

以淘宝为例（B2B模式），淘宝为了防止店铺卖出商品之后，偷偷修改 SKU 商品信息，于是 将 SKU 的商品信息与商品的搜索权重绑定 在一起，只要修改 SKU 商品信息就会影响该商品的搜索权重，甚至严重的话会下架商品。例如在购买了某个商品（【进口】奶粉），当发现被骗的时候去维权，商家连夜修改了商品信息（将【进口】这个参数配置给去掉了），就会造成前后矛盾纠纷。淘宝为了防止这种情况，将SKU的商品信息和权重进行绑定，在一定程度上遏制了这种操作。

但并不是所有的的信息都会触发权重的变更，比如给这件衣服添加尺码和颜色，因为尺码和颜色是一个新的 sku。对于新零售平台来说，B2C模式中修改 SKU 对权重的影响不大。但是 B2B 平台，修改 SKU 对商品权重影响很大

SKU 表的设计

传统思路设计

主键	商品名称	CPU	内存	......	保质期	尺码	颜色
1	小米9	骁龙855	64GB	...	/	/	白色
2	雀巢咖啡	/	/	...	10天	/	/
3	T恤	/	/	...	/	XL	红色

传统数据表的思路，是将所有SKU属性罗列在一张表中，但这样可能会涉及到一个问题：字段属性并没有得到充分利用，即每种商品的类型不同，其对应的属性不同，如果将所有的属性字段都定义在同一张表中，就会造成对于某些商品而言并不会用到一些无关的属性，就会导致属性的存储特别“散乱”。

据此，可能会进一步思考，是否可以考虑将商品按照不同的类型进行拆分，不同类型的商品类型为其配置相应的规格参数配置（对照【品类表】 =》【参数表】），参考上述内容重新构建表设计思路：

手机类型：CPU、内存、尺寸、电池等
食品类型：保质期、规格大小等
服饰类型：颜色、尺码等

据此思路，优化后的表设计思路说明如下：产品表、商品表、品类表、参数表

一个产品归属于某个品类，这个品类下关联该品类的多个参数规格，一个产品下有多个商品信息，则经由这4个表关系关联，可以构建出一个SKU商品的所有参数信息

数据结构设计

结合上述设计思路，构建数据表结构设计

1.【品类表】和【参数表】设计

品类表：主键ID、品类编号、品类名称
- 思考为什么有主键ID还需要品类编号？=》可以理解为品类编号是业务层的一个设计，仅根据主键ID无法直接推断是什么品类，在实际业务中一般通过业务编号来划分不同的品类，例如给品类编号划分区域
  - 1~1000：电子类
  - 1200~1300：服装类
  - 1300+：.... 其他等等 ......

# 创建品类表并插入数据
create table t_spec_group
(
    id     int unsigned primary key auto_increment comment '主键',
    spg_id int unsigned not null comment '品类编号',
    `name` varchar(200) not null comment '品类名称',
    unique index unq_spg_id (spg_id),
    unique index unq_name (`name`),
    index idx_spg_id (spg_id)
) comment ='品类表';

INSERT INTO t_spec_group (id, spg_id, name) VALUES (1, 10001, '手机');
INSERT INTO t_spec_group (id, spg_id, name) VALUES (2, 10002, '手机线');
INSERT INTO t_spec_group (id, spg_id, name) VALUES (3, 10003, '手机电池');
INSERT INTO t_spec_group (id, spg_id, name) VALUES (4, 11001, '液晶电视');
INSERT INTO t_spec_group (id, spg_id, name) VALUES (5, 11002, '投影电视');

参数表：主键ID、品类编号、参数编号、参数名称、是否为数字？、单位名称（0）、可搜索、是否通用参数、参数值（0） ...
- 品类编号：用于关联品类表信息
- 参数编号：在某一品类下的排名计数器概念（一般用于排序）
- 参数名是否为数字？：用于区分参数的类型，例如数字或者文本类型
- 单位名称：参数单位，(0)表示可以有空值
- 可搜索：用于控制参数是否可用作搜索条件，例如常见的一些搜索场景，大类下还有有一些可供选择的细分的搜索选项（例如搜索电视类，细分搜索选项提供尺寸、颜色等）
- 通用参数：表示该参数在页面上是否要显示在主要位置
- 参数值：提供可选范畴（主要用于限制数据输入）

# 创建参数表
create table t_spec_param
(
    id        int unsigned primary key auto_increment comment '主键',
    spg_id    int unsigned not null comment '品类编号',
    spp_id    int unsigned not null comment '参数编号',
    `name`    varchar(200) not null comment '参数名称',
    `numeric` tinyint(1)   not null comment '是否为数字参数',
    unit      varchar(200) comment '单位（量词语）',
    generic   tinyint(1)   not null comment '是否为通用参数',
    searching boolean      not null comment '是否用于通用搜素',
    segements varchar(500) comment '参数值',
    index idx_spg_id (spg_id),
    index idx_spp_id (spp_id)
) comment ='参数表';

# 参数数据插入
INSERT INTO t_spec_param (id, spg_id, spp_id, name, `numeric`, unit, generic, searching, segements) VALUES (1, 10001, 1, 'CPU', 0, null, 1, 0, null);
INSERT INTO t_spec_param (id, spg_id, spp_id, name, `numeric`, unit, generic, searching, segements) VALUES (2, 10001, 2, '运存', 1, 'GB', 1, 1, null);
INSERT INTO t_spec_param (id, spg_id, spp_id, name, `numeric`, unit, generic, searching, segements) VALUES (3, 10001, 3, '内存', 1, 'GB', 1, 1, null);
INSERT INTO t_spec_param (id, spg_id, spp_id, name, `numeric`, unit, generic, searching, segements) VALUES (4, 10001, 4, '屏幕尺寸', 1, '英寸', 1, 1, null);
INSERT INTO t_spec_param (id, spg_id, spp_id, name, `numeric`, unit, generic, searching, segements) VALUES (5, 10001, 5, '电池', 1, '毫安时', 1, 0, null);
INSERT INTO t_spec_param (id, spg_id, spp_id, name, `numeric`, unit, generic, searching, segements) VALUES (6, 11001, 1, '屏幕尺寸', 1, '英寸', 1, 1, null);
INSERT INTO t_spec_param (id, spg_id, spp_id, name, `numeric`, unit, generic, searching, segements) VALUES (7, 11001, 2, '长度', 1, '厘米', 1, 0, null);
INSERT INTO t_spec_param (id, spg_id, spp_id, name, `numeric`, unit, generic, searching, segements) VALUES (8, 11001, 3, '高度', 1, '厘米', 1, 0, null);
INSERT INTO t_spec_param (id, spg_id, spp_id, name, `numeric`, unit, generic, searching, segements) VALUES (9, 11001, 4, '宽度', 1, '厘米', 1, 0, null);
INSERT INTO t_spec_param (id, spg_id, spp_id, name, `numeric`, unit, generic, searching, segements) VALUES (10, 11001, 5, '分辨率', 0, '像素', 1, 1, '720P\\1080P\\4K\\8K');

2.【品牌表】、【分类表】、【分类和品牌关联表】设计

产品有归属的分类和品牌，此处先设计【品牌表】和【分类关系表】

品牌表：主键ID、品牌名称、商标图片（0）、首字母
- 品牌名称：品牌名唯一
- 商标图片：存储商标图片的地址信息
- 首字母：冗余首字母字段，在搜索的时候列出品牌可以按照首字母进行排列（也可通过品牌名称动态生成，此处多冗余一个字段存储首字母信息并添加索引用于提升检索效率）

# 创建品牌表
create table t_brand
(
    id     int unsigned primary key auto_increment comment '主键',
    `name` varchar(200) not null comment '名称',
    image  varchar(500) comment '图片网址',
    letter char(1)      not null comment '单位（量词语）',
    unique unq_name (`name`),
    index idx_letter (letter)
) comment ='品牌表';

# 数据初始化
INSERT INTO t_brand (id, name, image, letter) VALUES (1, '联想', null, 'L');
INSERT INTO t_brand (id, name, image, letter) VALUES (2, '华为', null, 'H');
INSERT INTO t_brand (id, name, image, letter) VALUES (3, '小米', null, 'A');
INSERT INTO t_brand (id, name, image, letter) VALUES (4, '苹果', null, 'A');
INSERT INTO t_brand (id, name, image, letter) VALUES (5, 'OPPO', null, 'O');
INSERT INTO t_brand (id, name, image, letter) VALUES (6, '三星', null, 'S');
INSERT INTO t_brand (id, name, image, letter) VALUES (7, 'LG', null, 'L');
INSERT INTO t_brand (id, name, image, letter) VALUES (8, 'vivo', null, 'V');
INSERT INTO t_brand (id, name, image, letter) VALUES (9, '飞利浦', null, 'F');
INSERT INTO t_brand (id, name, image, letter) VALUES (10, '红米', null, 'H');
INSERT INTO t_brand (id, name, image, letter) VALUES (11, 'IMB', null, 'I');
INSERT INTO t_brand (id, name, image, letter) VALUES (12, '戴尔', null, 'D');

分类表（涉及多级分类存储）：主键ID、分类名称、父节点ID（0）、是否为父节点、排名指数
- 分类名称：可重复
- 父节点ID（0）：对于顶级节点而言是没有父节点的
- 是否为父节点：区分当前节点是否为父节点（是：true；否：false）
- 排名指数：相当于搜索权重

create table t_category
(
    id        int unsigned primary key auto_increment comment '主键',
    `name`    varchar(200) not null comment '分类名称',
    parent_id int unsigned comment '上级分类ID',
    if_parent tinyint(1)   not null comment '是否包含下级分类',
    sort      int unsigned not null comment '排名指数',
    index idx_parent_id (parent_id),
    index idx_sort (sort)
) comment ='商品分类表';

# 数据初始化
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (1, '手机/数码/配件', null, 1, 1);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (2, '手机通讯', 1, 1, 1);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (3, '手机', 2, 0, 1);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (4, '手机配件', 1, 1, 2);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (5, '移动电源', 4, 0, 5);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (6, '蓝牙耳机', 4, 0, 2);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (7, '保护壳', 4, 0, 3);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (8, '数码配件', 1, 1, 10);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (9, '存储卡', 8, 0, 10);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (10, '读卡器', 8, 0, 1);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (11, '电脑/办公/外设', null, 1, 1);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (12, '电脑整机', 11, 1, 1);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (13, '笔记本', 12, 0, 1);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (14, '台式电脑', 12, 0, 1);
INSERT INTO t_category (id, name, parent_id, if_parent, sort) VALUES (15, '平板电脑', 12, 0, 1);

分类和品牌关联表（多对多关系：一个品牌可以有多个分类，一个分类可以分给多个品牌）：由分类ID、品牌ID构建的复合主键
- 例如：手机分类可以关联小米、华为、苹果等（一个分类下可以看到有哪些品牌在售卖此类东西）

create table t_category_brand
(
    category_id int unsigned comment '分类 ID',
    brand_id    int unsigned comment '品牌 ID',
    primary key (category_id, brand_id)
) comment ='分类与品牌关联表';

# 数据初始化
INSERT INTO t_category_brand (category_id, brand_id) VALUES (3, 1);
INSERT INTO t_category_brand (category_id, brand_id) VALUES (3, 2);
INSERT INTO t_category_brand (category_id, brand_id) VALUES (3, 3);
INSERT INTO t_category_brand (category_id, brand_id) VALUES (3, 4);
INSERT INTO t_category_brand (category_id, brand_id) VALUES (3, 5);
INSERT INTO t_category_brand (category_id, brand_id) VALUES (3, 6);
INSERT INTO t_category_brand (category_id, brand_id) VALUES (3, 8);
INSERT INTO t_category_brand (category_id, brand_id) VALUES (3, 10);
INSERT INTO t_category_brand (category_id, brand_id) VALUES (13, 1);
INSERT INTO t_category_brand (category_id, brand_id) VALUES (13, 2);
INSERT INTO t_category_brand (category_id, brand_id) VALUES (13, 12);

3.【产品表】和【商品表】设计

产品表：产品ID、标题、副标题（0）、分类编号、品牌编号（0）、品类编号、是否上架、是否有效、创建时间、最后修改时间
- 标题、副标题：一般不会在标题、副标题上创建索引（查询太慢），而是引入中文分词技术（MySQL自带的分词技术对中文分词不太友好，一般借助第三方组件完成）
- 品牌编号（0）：品牌可为空（一些散装的产品可能没有品牌信息，例如花生等）
- 品类编号：产品关联品类编号
- 是否上架：产品是否上架
- 是否有效：逻辑删除标识（产品记录可能关联了很多订单、商品信息，如果直接将其删除就会导致一些记录关联失败，此处引入逻辑删除概念，将其”隐藏“进而变相实现逻辑删除操作）
- 创建时间：不能为空，默认值为当前时间
- 最后修改时间：不能为空，取系统时间为默认值

create table t_spu
(
    id               int unsigned primary key auto_increment comment '主键',
    title            varchar(200) not null comment '标题',
    sub_title        varchar(200) comment '副标题',
    category_id      int unsigned not null comment '分类ID',
    brand_id         int unsigned comment '品牌ID',
    spg_id           int unsigned comment '品类ID',
    saleable         boolean      not null comment '是否上架',
    valid            boolean      not null comment '是否有效',
    create_time      timestamp    not null default now() comment '添加时间',
    last_update_time timestamp    not null default now() comment '最后修改时间',
    index idx_category_id (category_id),
    index idx_brand_id (brand_id),
    index idx_spg_id (spg_id),
    index idx_saleable (saleable),
    index idx_valid (valid)

) comment ='产品表';

# 数据初始化
INSERT INTO t_spu (id, title, sub_title, category_id, brand_id, spg_id, saleable, valid, create_time, last_update_time) VALUES (1, '小米9', null, 3, 3, 10001, 1, 1, '2020-05-19 18:53:41', '2020-05-19 18:53:41');

商品表：主键ID、标题、产品编号、图片（0）、价格、参数、是否上架、是否有效、创建时间、最后修改时间
- 标题：一般不添加索引，而是使用中文分词来实现检索优化
- 图片（0）：图片地址信息（可以为空），使用json类型保存商品参数信息（desc：商品描述图、facade：商品展示图）
- 价格：decimal 无符号类型
  - 思考促销场景中是否需要引入"促销价格"字段：具体结合业务场景分析（例如会员制下每个会员等级享受的折扣不同，则促销价不适合定义在商品表中，可以在客户浏览商品的时候动态根据客户等级动态计算促销价格）
- 参数：此处的参数设定与关联品类涉及的参数是相互对照的，根据关联品类相关的参数信息，此处对照填充相应的参数值信息，以json形式保存

# 创建数据表
create table t_sku
(
    id               int unsigned primary key auto_increment comment '主键',
    spu_id           int unsigned            not null comment '产品ID',
    title            varchar(200)            not null comment '标题',
    images           json comment '商品图片',
    price            decimal(10, 2) unsigned not null comment '价格',
    param            json                    not null comment '参数',
    saleable         boolean                 not null comment '是否上架',
    valid            boolean                 not null comment '是否有效',
    create_time      timestamp               not null default now() comment '添加时间',
    last_update_time timestamp               not null default now() comment '最后修改时间',
    index idx_spu_id (spu_id),
    index idx_saleable (saleable),
    index idx_valid (valid)
) comment ='商品表';

# 初始化数据
INSERT INTO t_sku (id, spu_id, title, images, price, param, saleable, valid, create_time, last_update_time) VALUES (1, 1, 'Xiaomi/小米 小米9 8GB+128GB 全息幻彩紫 移动联通电信全网通4G手机', '{"desc": ["http://127.0.0.1/1.jpg", "http://127.0.0.1/2.jpg"], "facade": ["http://127.0.0.1/3.jpg", "http://127.0.0.1/4.jpg"]}', 3299.00, '{"CPU": "骁龙855", "内存": "128", "电池": 4000, "运存": 8, "屏幕尺寸": 6.39}', 1, 1, '2020-05-19 19:17:16', '2020-05-19 19:17:16');
INSERT INTO t_sku (id, spu_id, title, images, price, param, saleable, valid, create_time, last_update_time) VALUES (2, 1, 'Xiaomi/小米 小米9 8GB+128GB 全息幻彩蓝 移动联通电信全网通4G手机', '{"desc": ["http://127.0.0.1/1.jpg", "http://127.0.0.1/2.jpg"], "facade": ["http://127.0.0.1/3.jpg", "http://127.0.0.1/4.jpg"]}', 3299.00, '{"CPU": "骁龙855", "内存": "128", "电池": 4000, "运存": 8, "屏幕尺寸": 6.39}', 1, 1, '2020-05-19 19:17:16', '2020-05-19 19:17:16');
INSERT INTO t_sku (id, spu_id, title, images, price, param, saleable, valid, create_time, last_update_time) VALUES (3, 1, 'Xiaomi/小米 小米9 6GB+128GB 全息幻彩蓝 移动联通电信全网通4G手机', '{"desc": ["http://127.0.0.1/1.jpg", "http://127.0.0.1/2.jpg"], "facade": ["http://127.0.0.1/3.jpg", "http://127.0.0.1/4.jpg"]}', 2999.00, '{"CPU": "骁龙855", "内存": "128", "电池": 4000, "运存": 6, "屏幕尺寸": 6.39}', 1, 1, '2020-05-19 19:17:16', '2020-05-19 19:17:16');
INSERT INTO t_sku (id, spu_id, title, images, price, param, saleable, valid, create_time, last_update_time) VALUES (4, 1, 'Xiaomi/小米 小米9 6GB+128GB 深空灰 移动联通电信全网通4G手机', '{"desc": ["http://127.0.0.1/1.jpg", "http://127.0.0.1/2.jpg"], "facade": ["http://127.0.0.1/3.jpg", "http://127.0.0.1/4.jpg"]}', 2999.00, '{"CPU": "骁龙855", "内存": "128", "电池": 4000, "运存": 6, "屏幕尺寸": 6.39}', 1, 1, '2020-05-19 19:17:16', '2020-05-19 19:17:16');

4.【库存】设计

【库存】信息的设计可结合实际业务场景去定义

场景1：如果新零售系统没有分店，则可以考虑直接将库存定义到商品表中

编号	名称	产品编号	参数	库存
1	A商品	1001	...	25
2	B商品	1002	...	100
3	C商品	1003	...	36

场景2：参考苏宁易购，在全国创建了很多仓库，每个仓库对应每个零售店的库存

引入商品表、仓库表、零售店表，分别构建多对多关系

商品与仓库关联：商品在哪些仓库中有多少库存？
零售店与商品关联：商品在哪些零售店中有多少库存？
零售店与仓库关联：零售店和仓库中都有省份和城市属性，通过地址属性进行匹配，由系统动态计算从哪个仓库发货策略更优

【省份表】、【城市表】设计

省份表：主键ID、省份名称
- 省份名称：创建唯一性约束（该表涉及身份数据很少，考虑索引的创建维护成本，此处不需要在省份上创建索引）
城市表：主键ID、城市名称、关联省份ID

# 省份表、城市表 设计
create table t_province
(
    id       int unsigned primary key auto_increment comment '主键',
    province varchar(200) not null comment '省份',
    unique unq_province (province)
) comment '省份表';

create table t_city
(
    id          int unsigned primary key auto_increment comment '主键',
    cite        varchar(200) not null comment '城市',
    province_id int unsigned not null comment '省份ID'
);

# 初始化数据
INSERT INTO t_province (id, province) VALUES (2, '上海');
INSERT INTO t_province (id, province) VALUES (1, '北京');
INSERT INTO t_province (id, province) VALUES (6, '吉林');
INSERT INTO t_province (id, province) VALUES (3, '天津');
INSERT INTO t_province (id, province) VALUES (8, '山东');
INSERT INTO t_province (id, province) VALUES (9, '江苏');
INSERT INTO t_province (id, province) VALUES (10, '浙江');
INSERT INTO t_province (id, province) VALUES (5, '辽宁');
INSERT INTO t_province (id, province) VALUES (4, '重庆');
INSERT INTO t_province (id, province) VALUES (7, '黑龙江');

INSERT INTO t_city (id, cite, province_id) VALUES (1, '沈阳', 5);
INSERT INTO t_city (id, cite, province_id) VALUES (2, '大连', 5);
INSERT INTO t_city (id, cite, province_id) VALUES (3, '鞍山', 5);
INSERT INTO t_city (id, cite, province_id) VALUES (4, '长春', 6);
INSERT INTO t_city (id, cite, province_id) VALUES (5, '吉林', 6);
INSERT INTO t_city (id, cite, province_id) VALUES (6, '哈尔滨', 7);
INSERT INTO t_city (id, cite, province_id) VALUES (7, '齐齐哈尔', 7);
INSERT INTO t_city (id, cite, province_id) VALUES (8, '牡丹江', 7);

【仓库表】、【库存关联表】设计

仓库表（仓库信息表）：主键ID、城市编号、仓库地址、联系电话
- 城市编号：city_id，仓库在哪个城市（构建索引，提升查询效率）

# 创建表
create table t_warehouse
(
    id      int unsigned primary key auto_increment comment '主键',
    city_id int unsigned not null comment '城市ID',
    address varchar(200) not null comment '地址',
    tel     varchar(20)  not null comment '电话',
    index idx_city_id (city_id)
) comment '仓库表';

# 初始化数据
INSERT INTO t_warehouse (id, city_id, address, tel) VALUES (1, 1, '辽宁省沈阳市沈河区青年大街100号', '024-12345678');
INSERT INTO t_warehouse (id, city_id, address, tel) VALUES (2, 1, '辽宁省沈阳市皇姑区崇山路41号', '024-22331234');
INSERT INTO t_warehouse (id, city_id, address, tel) VALUES (3, 2, '辽宁省沈阳市西岗区五四路38号', '0411-12345678');
INSERT INTO t_warehouse (id, city_id, address, tel) VALUES (4, 2, '辽宁省沈阳市沙河口兴云街1号', '0411-98213210');

关联表（仓库与SKU关联表）：由仓库ID和SKU_ID构建复合主键、库存数量、库存单位
- 复合主键：由由仓库ID和SKU_ID进行构建，以杜绝一种商品在同一个仓库中出现两次

# 创建表
create table t_warehouse_sku
(
    warehouse_id int unsigned comment '主键',
    sku_id       int unsigned comment '商品ID',
    num          int unsigned not null comment '库存数量',
    unit         varchar(20)  not null comment '库存单位',
    primary key (warehouse_id, sku_id)
) comment '仓库商品库存表';

# 初始化
INSERT INTO t_warehouse_sku (warehouse_id, sku_id, num, unit) VALUES (1, 1, 20, '部');
INSERT INTO t_warehouse_sku (warehouse_id, sku_id, num, unit) VALUES (1, 2, 15, '部');
INSERT INTO t_warehouse_sku (warehouse_id, sku_id, num, unit) VALUES (1, 3, 40, '部');
INSERT INTO t_warehouse_sku (warehouse_id, sku_id, num, unit) VALUES (1, 4, 0, '部');
INSERT INTO t_warehouse_sku (warehouse_id, sku_id, num, unit) VALUES (2, 1, 70, '部');
INSERT INTO t_warehouse_sku (warehouse_id, sku_id, num, unit) VALUES (2, 2, 0, '部');
INSERT INTO t_warehouse_sku (warehouse_id, sku_id, num, unit) VALUES (2, 3, 5, '部');
INSERT INTO t_warehouse_sku (warehouse_id, sku_id, num, unit) VALUES (2, 4, 19, '部');

【零售店】、【零售店库存关联表】设计

零售店（零售店信息表）：主键ID、城市编号、零售店地址、联系方式

# 创建表
create table t_shop
(
    id      int unsigned primary key auto_increment comment '主键',
    city_id int unsigned not null comment '城市ID',
    address varchar(200) not null comment '地址',
    tel     varchar(20)  not null comment '电话',
    index idx_city_id (city_id)
) comment '零售店';

# 初始化数据
INSERT INTO t_shop (id, city_id, address, tel) VALUES (1, 1, '辽宁省沈阳市黄河北大街12号', '024-12345678');
INSERT INTO t_shop (id, city_id, address, tel) VALUES (2, 1, '辽宁省沈阳市皇姑区长江街40号', '024-22331234');
INSERT INTO t_shop (id, city_id, address, tel) VALUES (3, 2, '辽宁省沈阳市西区卫工街19号', '0411-12345678');
INSERT INTO t_shop (id, city_id, address, tel) VALUES (4, 2, '大连市西岗区五四路38号', '0411-98213210');
INSERT INTO t_shop (id, city_id, address, tel) VALUES (5, 2, '大连市沙河口兴云街1号', '0411-98213210');

关联表（零售店与SKU关联表）：由零售店ID和SKU_ID构建复合主键、库存数量、库存单位
- 复合主键：由由零售店ID和SKU_ID进行构建，以杜绝一种商品在同一个零售店中出现两次

# 创建表
create table t_shop_sku
(
    shop_id int unsigned comment '主键',
    sku_id  int unsigned comment '商品ID',
    num     int unsigned not null comment '库存数量',
    unit    varchar(20)  not null comment '库存单位',
    primary key (shop_id, sku_id)
) comment '零售店与库存关联表';

# 初始化数据
INSERT INTO t_shop_sku (shop_id, sku_id, num, unit) VALUES (1, 1, 3, '部');
INSERT INTO t_shop_sku (shop_id, sku_id, num, unit) VALUES (1, 2, 3, '部');
INSERT INTO t_shop_sku (shop_id, sku_id, num, unit) VALUES (1, 3, 1, '部');
INSERT INTO t_shop_sku (shop_id, sku_id, num, unit) VALUES (1, 4, 0, '部');
INSERT INTO t_shop_sku (shop_id, sku_id, num, unit) VALUES (2, 1, 3, '部');
INSERT INTO t_shop_sku (shop_id, sku_id, num, unit) VALUES (2, 2, 0, '部');
INSERT INTO t_shop_sku (shop_id, sku_id, num, unit) VALUES (2, 3, 0, '部');
INSERT INTO t_shop_sku (shop_id, sku_id, num, unit) VALUES (2, 4, 1, '部');

5.【客户表】设计

构建客户信息，拆分不同等级的客户信息维护

【会员等级表】设计

会员等级表：主键ID、会员等级、折扣力度
- level：会员等级，等级名称（例如金牌、银牌等）
- discount：折扣，会员对应等级享受的折扣力度

# 创建表
create table t_level
(
    id       int unsigned primary key auto_increment comment '主键',
    level    varchar(200)            not null comment '等级',
    discount decimal(10, 2) unsigned not null comment '折扣'
) comment '会员等级表';

# 初始化数据
INSERT INTO t_level (id, level, discount) VALUES (1, '铜牌会员', 0.99);
INSERT INTO t_level (id, level, discount) VALUES (2, '银牌会员', 0.98);
INSERT INTO t_level (id, level, discount) VALUES (3, '金牌会员', 0.97);
INSERT INTO t_level (id, level, discount) VALUES (4, '白金会员', 0.95);
INSERT INTO t_level (id, level, discount) VALUES (5, '砖石会员', 0.92);

【客户表】设计

客户表：主键ID、客户名称、密码、微信号、联系方式、会员等级、创建时间、最近修改时间
- username：用户名
- password：密码（采用AES算法加密：用于加解密；MD5只能加密不能解密，无法扩展密码找回功能）
- wechat：微信号
- level_id：会员等级（关联会员等级ID）

# 创建表
create table t_customer
(
    id               int unsigned primary key auto_increment comment '主键',
    username         varchar(200)  not null comment '用户名',
    password         varchar(2000) not null comment '密码(AES加密)',
    wechat           varchar(200) comment '微信号',
    tel              char(11) comment '手机号',
    level_id         int unsigned comment '会员等级ID',
    create_time      timestamp     not null default now() comment '添加时间',
    last_update_time timestamp     not null default now() comment '最后修改时间',
    index idx_username (username),
    unique unq_username (username)
) comment '客户表';

# 初始化数据
INSERT INTO t_customer (id, username, password, wechat, tel, level_id, create_time, last_update_time) VALUES (1, 'scott123', '896349E30ED6B4CF0E4354716CA997D9', '', null, 1, '2020-05-19 21:00:33', '2020-05-19 21:00:33');

# mysql中支持的函数提供密码的加解密功能
-- aes_encrypt(密码,密钥)，输出的是二进制数据
-- hex 将二进制数据转成 16 进制数据
select hex(aes_encrypt('123456', left('scott123', 7)))

-- 下面这个是解密
select aes_decrypt(unhex('896349E30ED6B4CF0E4354716CA997D9'),left('scott123', 7));

# 数据插入
insert into t_customer(username, password, wechat, tel)
values ('jack123',
        hex(aes_encrypt('123456', left('jack123', 7))),
        'jack123',
        '12345678');

【客户收货地址表】设计

客户收货地址表：主键ID、客户ID、收件人名称、收件人联系方式、收件地址、是否用作缺省收货地址
- customer_id：关联客户ID，创建索引用于提升查询效率
- prime：是否用作缺省收货地址

# 创建表
create table t_customer_address
(
    id          int unsigned primary key auto_increment comment '主键',
    customer_id int unsigned not null comment '客户ID',
    name        varchar(200) not null comment '收货人姓名',
    tel         char(11) comment '收货人手机号',
    address     varchar(200) not null comment '收货地址',
    prime       boolean      not null comment '是否用当前地址记录作为默认收货地址',
    index idx_customer_id (customer_id)
) comment '客户表收货地址表';

# 初始化数据
INSERT INTO t_customer_address (id, customer_id, name, tel, address, prime) VALUES (1, 1, '陈浩', '12345678901', '辽宁省大连市高兴区6号9#11-1', 1);
INSERT INTO t_customer_address (id, customer_id, name, tel, address, prime) VALUES (2, 2, '李娜', '12345678902', '辽宁省大连市沙盒口区星月街17号2#1-3', 0);

6.【购物券表】设计

在购物的时候可以选择使用购物券，每张购物券有其相应的使用规则：

购物卷有使用期限，而且一个订单只可以用一张购物卷
购物卷与客户记录关联，需要客户自己领取，而且客户可以领取多张不同的购物卷

【购物券表】设计

购物券表：主键ID、购物券金额、购物券使用限制条件、使用期限（开始时间、结束时间）、购物券发放最大张数
- denomination：购物卷金额
- condition：超过多少金额才能使用购物卷
- start_date 和 end_date ：使用期限，注意：只有日期，没有时间
- max_num：购物卷最多发放多少张。没有值则不限制

# 创建表
create table t_voucher
(
    id          int unsigned primary key auto_increment comment '主键',
    deno        decimal(10, 2) unsigned not null comment '面值',
    `condition` decimal(10, 2) unsigned not null comment '订单满多少钱可以使用',
    start_date  date comment '起始日期',
    end_date    date comment '截止日期',
    max_num     int comment '购物卷发放最大数量'
) comment '购物卷表';

# 初始化数据
INSERT INTO t_voucher (id, deno, `condition`, start_date, end_date, max_num) VALUES (1, 50.00, 1000.00, '2020-06-06', '2020-06-25', 1000);
INSERT INTO t_voucher (id, deno, `condition`, start_date, end_date, max_num) VALUES (2, 20.00, 500.00, '2020-06-06', '2020-06-25', null);

【购物券与客户关联表】设计

购物券与客户关联表：主键ID、购物券ID、客户ID
- 该表主键不是复合主键构成，一个人可以领取多张同样的购物券（具体结合业务适配，例如要购买一堆东西可以拆成多单，每单分别用一张购物券更加划算）

# 创建表
create table t_voucher_customer
(
    id         int unsigned primary key auto_increment comment '主键',
    voucher_id int unsigned not null comment '购物卷ID',
    customer   int unsigned not null comment '客户ID'
) comment '客户关联购物卷表';

# 初始化数据
INSERT INTO t_voucher_customer (id, voucher_id, customer) VALUES (1, 1, 1);
INSERT INTO t_voucher_customer (id, voucher_id, customer) VALUES (2, 1, 1);
INSERT INTO t_voucher_customer (id, voucher_id, customer) VALUES (3, 1, 1);
INSERT INTO t_voucher_customer (id, voucher_id, customer) VALUES (4, 2, 1);
INSERT INTO t_voucher_customer (id, voucher_id, customer) VALUES (5, 2, 1);

对于购物卷，也有很多不同的业务场景，新零售做的是 B2C 的业务，没有第三方商铺，在线上和线下都可以使用。而 B2B 则是，对应商品的购物卷只能在对应的商品内使用。对于京东这种混合 B2C 和 B2B 的业务场景，更复杂

7.【订单表】设计

订单表设计核心：一个订单中有多个订单明细，一般设定一个订单主表、订单明细表（订单详情表）

思考：针对一张订单中可以包含多个商品记录，可不可以用 JSON 存储这些商品信息？ =》答案自然是不合适的

因为MySQL 5.7+ 引入的 JSON 字段适合存储数据，不适合检索数据。存储的数据只是用来页面展示，而 不用来做搜索条件，在前面设计的表中，很少对字符串的字段做索引（在检索的时候是通过引入一些第三方检索组件来提高检索效率）

订单表：主键ID、订单流水号、订单类型、关联零售店、关联客户、订单金额、支付方式、订单状态、邮费、订单总重量、购物券ID、创建时间
- code：订单流水号；规则可自定义
  订单的业务编号（由业务人员去制定）：流水号尾部以字母 A 结尾表示液体，B 结尾表示易碎品等。还可以包含订单的日期、和时间，消费小票打印出来后，阅读流水号可以知道是哪一类的商品、什么时候生成的订单、是否加急发货等。
- type：订单类型（如为 1：表示线下销售商品，为 2 为线上销售的商品）
- shop_id：店铺ID（如果线上卖出的，可以为空）
- customer_id：客户ID（主要用来关联会员等级等。如果用户在线下购买的商品，不是会员可以为空）
- amount：订单的总价格
- payment_type：订单的支付类型（如：借记卡付款、信用卡、微信支付、现今支付等）
- status：订单状态（如：未付款、已付款、已发货、已签收等）
- postage：邮费
- weight：订单总重量，单位为克（用于程序中进行业务校验，例如京东超过多少重量加收运费，预留这个字段用作程序业务校验）
- voucher_id：购物卷ID（通常情况下，一个订单只能使用一张购物卷，具体结合实际业务进行设计）
- create_time：订单创建的时间

# 创建表
create table t_order
(
    id           int unsigned primary key auto_increment comment '主键',
    `code`       varchar(200)            not null comment '流水号',
    type         tinyint unsigned        not null comment '订单类型：1实体销售，2网络销售',
    shop_id      int unsigned comment '零售店ID',
    customer_id  int unsigned comment '会员ID',
    amount       decimal(10, 2) unsigned not null comment '总金额',
    payment_type tinyint unsigned        not null comment '支付方式：1借记卡、2信用卡、3微信、4支付宝、5现金',
    `status`     tinyint unsigned        not null comment '状态：1未付款、2已付款、3已发货、4已签收',
    postage      decimal(10, 2) unsigned comment '邮费',
    weight       int unsigned comment '重量：单位克',
    voucher_id   int unsigned comment '购物券ID',
    create_time  timestamp               not null default now(),
    index idx_code (`code`),
    index idx_customer_id (customer_id),
    index idx_status (`status`),
    index idx_create_time (create_time),
    index idx_type (type),
    index idx_shop_id (shop_id),
    unique unq_code (`code`)
) comment '订单表';

# 初始化数据
INSERT INTO t_order (id, code, type, shop_id, customer_id, amount, payment_type, status, postage, weight, voucher_id, create_time) VALUES (1, 'CX0000000120160522', 1, 3, 1, 2999.00, 5, 2, null, null, null, '2020-05-19 23:14:10');

订单详情表：复合主键（order_id、sku_id）、商品原价、购买商品的实际价格、数量
- order_id 和 sku_id ：复合主键，一个订单中不会出现两个相同的商品，可以用数量去记录多件
- price：商品原价
- actual_price：购买商品的实际价格
- num：购买商品的实际数量

# 创建表
create table t_order_detail
(
    order_id     int unsigned            not null comment '订单ID',
    sku_id       int unsigned            not null comment '商品ID',
    price        decimal(10, 2) unsigned not null comment '原价格',
    actual_price decimal(10, 2) unsigned not null comment '实际购买价格',
    num          int unsigned            not null comment '购买数量',
    primary key (order_id, sku_id)
) comment '订单详情表'

# 初始化数据
INSERT INTO t_order_detail (order_id, sku_id, price, actual_price, num) VALUES (1, 3, 2999.00, 2999.00, 1);

8.【员工表】、【用户表】设计

新零售系统具有 进销存 属性，所以需要先设计出员工表、用户表、和角色表。比如，给客户快递商品：谁做的质检、谁发的快递都要有记录、还有仓库进货时，哪个员工验的货、还有当客户退货的时候，哪个员工处理的退货，等等的场景都需要员工参与

一个发货表对应多个员工
这一单有多个阶段流程，可能由多个不同的员工参与
员工表与用户表
用户表，是使用新零售管理系统的用户，因为并不是所有的员工都有权限使用新零售系统。比如：保洁、保安等员工是不使用新零售系统的。
用户表与角色表
零售店员与仓库管理员，是两个不同的角色，他们看到的数据与能操作的也是不同的。
最简单的方式是一个用户对应一个角色；如果对应多个角色就需要使用关联表
部门、职位与员工：除却上述设计，一个员工还可关联其归属部门和职位信息

【部门表】、【职位表】、【员工表】

部门表：主键ID、部门名称
- 部门名称：dname，唯一约束

# 数据表创建
create table t_dept
(
    id    int unsigned primary key auto_increment not null comment '主键',
    dname varchar(200)             not null comment '部门名称',
    unique unq_dname (dname)
) comment '部门表';

# 初始化数据
INSERT INTO t_dept (id, dname) VALUES (6, '售后部');
INSERT INTO t_dept (id, dname) VALUES (2, '总裁办');
INSERT INTO t_dept (id, dname) VALUES (5, '技术部');
INSERT INTO t_dept (id, dname) VALUES (4, '网商部');
INSERT INTO t_dept (id, dname) VALUES (1, '董事会');
INSERT INTO t_dept (id, dname) VALUES (3, '零售部');

职位表：主键ID、职位名称
- 职位名称：job，唯一约束

# 数据表创建
create table t_job
(
    id  int unsigned primary key auto_increment not null comment '主键',
    job varchar(200)             not null comment '职位名称',
    unique unq_job (job)
) comment '职位表';

# 初始化数据
INSERT INTO t_job (id, job) VALUES (4, '主管');
INSERT INTO t_job (id, job) VALUES (7, '保安');
INSERT INTO t_job (id, job) VALUES (8, '保管员');
INSERT INTO t_job (id, job) VALUES (6, '售货员');
INSERT INTO t_job (id, job) VALUES (5, '店长');
INSERT INTO t_job (id, job) VALUES (2, '总经理');
INSERT INTO t_job (id, job) VALUES (1, '董事长');
INSERT INTO t_job (id, job) VALUES (3, '部门经理');

员工表：主键ID、员工号、姓名、性别、婚否、学历、联系方式信息、关联职位、关联部门、关联上司编号、入职日期、离职日期、员工状态
- 员工号：根据公司员工编号命名规则进行制定
- 联系方式信息：地址、邮件、手机号等
- 员工离职日期：为空表示未离职
- 员工状态：休假、离职等

# 数据表创建
create table t_emp
(
    id        int unsigned primary key auto_increment comment '主键',
    wid       varchar(20)  not null comment '流水号',
    ename     varchar(20)  not null comment '员工姓名',
    sex       char(1)      not null comment '性别',
    married   boolean      not null comment '婚否',
    education tinyint      not null comment '学历：1大专、2本科、3研究生、4博士、5其他',
    tel       char(11) comment '电话号码',
    email     varchar(200) comment '邮箱',
    address   varchar(200) comment '员工住址',
    job_id    int unsigned not null comment '职位ID',
    dept_id   int unsigned not null comment '部门ID',
    mgr_id    int unsigned comment '员工上司ID',
    hiredate  date         not null comment '入职日期',
    termdate  date comment '离职日期',
    `status`  tinyint comment '员工状态：1在职、2休假、3离职、4死亡',
    index idx_job_id (job_id),
    index idx_dept_id (dept_id),
    index idx_status (`status`),
    index idx_mgr_id (mgr_id),
    unique unq_wid (wid)
) comment '员工表';

# 初始化数据
-- 忽略了董事长的数据，为了演示 mgr_id 为空，这里直接用一个普通员工设置了空
INSERT INTO t_emp (id, wid, ename, sex, married, education, tel, email, address, job_id, dept_id, mgr_id, hiredate, termdate, status) VALUES (1, 'S10010E', '李娜', '女', 1, 2, '18912345678', null, null, 5, 3, null, '2019-06-06', null, 1);
INSERT INTO t_emp (id, wid, ename, sex, married, education, tel, email, address, job_id, dept_id, mgr_id, hiredate, termdate, status) VALUES (2, 'S10014A', '流畅', '女', 1, 2, '18912345677', null, null, 6, 3, 1, '2020-06-06', null, 1);

【用户表】设计

一个用户关联相应角色，员工关联用户账号信息

角色表：主键ID、角色名称
- 角色名称：唯一约束

# 创建表
create table t_role
(
    id   int unsigned primary key auto_increment not null comment '主键',
    role varchar(20)              not null comment '角色名称',
    unique unq_role(role)
) comment ='角色表';
-- 在字段声明中使用 unique，mysql 会自动创建 unique 的索引，不过名称则是字段名，
-- 所以一般还是我们自己手动定义名称

# 初始化数据
INSERT INTO t_role (id, role) VALUES (6, '保管员');
INSERT INTO t_role (id, role) VALUES (5, '克服');
INSERT INTO t_role (id, role) VALUES (3, '售货员');
INSERT INTO t_role (id, role) VALUES (2, '管理员');
INSERT INTO t_role (id, role) VALUES (7, '质检员');
INSERT INTO t_role (id, role) VALUES (1, '超级管理员');
INSERT INTO t_role (id, role) VALUES (4, '零售店长');

用户表：主键ID、用户名称、用户密码、关联员工、关联角色、用户状态、创建时间、最后修改时间

# 创建表
create table t_user
(
    id               int unsigned primary key auto_increment not null comment '主键',
    username         varchar(200)                            not null comment '用户名',
    `password`       varchar(200)                            not null comment '密码（AES加密）',
    emp_id           int unsigned                            not null comment '员工ID',
    role_id          int unsigned                            not null comment '角色ID',
    `status`         tinyint unsigned                        not null comment '状态：1可用、2禁用',
    create_time      timestamp                               not null default now() comment '添加时间',
    last_update_time timestamp                               not null default now() comment '最后修改时间',
    unique unq_username (username),
    index idx_username (username),
    index idx_emp_id (emp_id),
    index idx_role_id (role_id),
    index idx_status (`status`)
) comment ='用户表';

# 初始化数据
INSERT INTO t_user (id, username, password, emp_id, role_id, status, create_time, last_update_time) VALUES (1, 'scott123', '896349E30ED6B4CF0E4354716CA997D9', 1, 4, 1, '2020-05-20 07:11:49', '2020-05-20 07:11:49');

9.【快递表】、【退货表】设计

快递表：主键ID、订单编号、商品信息（当前包括包括的商品）、质检员工、打包快递员工、快递单号、邮费、收货地址信息、发货仓库、快递公司
- order_id：订单编号（一个商品可以拆分多个包裹，但是 sku 是不同的）
- sku：这一个包裹发出了哪些商品
- qa_id：质检员工的ID
- de_id：打包快递的员工ID
- postid：快递单号；快递公司的快递单号
- price：邮费（订单里面记录的是总的快递邮费，此处是每一个包裹的邮费）
- address_id：用户的收获地址ID
- warehouse_id：发快递的仓库编号
- ecp：快递公司的编号

# 创建表
create table t_delivery
(
    id           int unsigned primary key auto_increment not null comment '主键',
    order_id     int unsigned                            not null comment '订单ID',
    sku          json                                    not null comment '商品',
    qa_id        int unsigned                            not null comment '质检员ID',
    de_id        int unsigned                            not null comment '发货员ID',
    postid       int unsigned                            not null comment '快递单号',
    price        decimal(10, 2) unsigned                 not null comment '快递费',
    address_id   int unsigned                            not null comment '收货地址ID',
    warehouse_id int unsigned                            not null comment '发货仓库ID',
    ecp          tinyint unsigned                        not null comment '快递公司编号',
    create_time  timestamp                               not null default now() comment '添加时间',
    index idx_order_id (order_id),
    index idx_qa_id (qa_id),
    index idx_de_id (de_id),
    index idx_postid (postid),
    index idx_address_id (address_id),
    index idx_warehouse_id (warehouse_id),
    index idx_ecp (ecp)
) comment ='快递表';

# 初始化数据
INSERT INTO t_delivery (id, order_id, sku, qa_id, de_id, postid, price, address_id, warehouse_id, ecp, create_time) VALUES (1, 2, '[3, 3]', 15, 17, 12333334, 60.00, 1, 1, 1, '2020-05-20 07:38:10');

# 此处可将快递单号修改为varchar类型比较方便
alter table t_delivery modify postid varchar(20) not null comment '快递单号'

退货表：主键ID、订单ID、退货商品、退货理由、退货质检员工号、退款金额、退款渠道、退货记录状态
- order_id：订单ID；只设置索引，没有唯一约束
  买了 10 件商品，退了其中一件，这会产生一条退货数据。后来再退货一件，又会产生一条
- sku：这次退货有哪些商品，用数组保存退货的商品编号
- reason：退货的原因
- qa_id：退货质检员的工号
- payment：退款金额未必是全额（具体结合实际场景，由质检员判定）
- payment_type：退款的渠道，现金、微信等
- status：退货记录状态

# 创建表
create table t_backstock
(
    id           int unsigned primary key auto_increment not null comment '主键',
    order_id     int unsigned                            not null comment '订单ID',
    sku          json                                    not null comment '退货商品',
    reason       varchar(200)                            not null comment '退货原因',
    qa_id        int unsigned                            not null comment '质检员ID',
    payment      decimal(10, 2) unsigned                 not null comment '退款金额',
    payment_type tinyint unsigned                        not null comment '退款范式：1借记卡、2信用卡、3微信、4支付宝、5现金',
    `status`     tinyint unsigned                        not null comment '状态：1退货成功、2无法退货',
    create_time  timestamp                               not null default now() comment '添加时间',
    index idx_order_id (order_id),
    index idx_qa_id (qa_id),
    index idx_status (`status`)
) comment ='退货表';

# 初始化数据
INSERT INTO t_backstock (id, order_id, sku, reason, qa_id, payment, payment_type, status, create_time) VALUES (1, 2, '[3]', '质量问题', 15, 2999.00, 5, 1, '2020-05-20 07:49:37');

10.【评价表】设计

评价表：主键ID、订单ID、商品编号、图片信息、评分信息、评价信息、创建时间
- order_id：订单编号
- sku_id：商品编号
- img：卖家晒的图片
- rating：评分，1 到 5 星
- comment：文字评价

# 创建表
create table t_rating
(
    id          int unsigned primary key auto_increment not null comment '主键',
    order_id    int unsigned                            not null comment '订单ID',
    sku_id      int unsigned                            not null comment '商品ID',
    img         json comment '买家嗮图',
    rating      tinyint unsigned                        not null comment '评分',
    `comment`   varchar(200) comment '评论',
    create_time timestamp                               not null default now() comment '添加时间',
    index idx_order_id (order_id),
    index idx_sku_id (sku_id),
    index idx_create_time (create_time)
) comment ='评价表';

# 初始化数据
INSERT INTO t_rating (id, order_id, sku_id, img, rating, comment, create_time) VALUES (1, 2, 3, '["http://192.22/1.jpg"]', 5, '很好用，非常好', '2020-05-20 09:01:08');

11.【供货商表】设计

供货商表：主键ID、供货商编号、供货商名称、供货商类型、联系人、电话、开户行名称、开户行账号、供货商地址、供货商状态
- code：供货商编号
- name：供货商名称
- type：供货商类型：如厂家、代理等
- link_man：联系人名称
- tel：电话
- bank_name：开户银行名称
  可以为空，由于供货商类型不同，比如农民，没有企业开户行
- bank_account：开户银行账户
- address：供货商地址
- status：供货商状态

# 创建表
create table t_supplier
(
    id           int unsigned primary key auto_increment not null comment '主键',
    `code`       varchar(200)                            not null comment '供货商编号',
    `name`       varchar(200)                            not null comment '供货商名称',
    `type`       tinyint unsigned                        not null comment '供货商类型：1厂家、2代理商、3个人',
    link_man     varchar(20)                             not null comment '联系人',
    tel          varchar(20)                             not null comment '联系电话',
    bank_name    varchar(200) comment '开户银行名称',
    bank_account varchar(200) comment '开户银行账户',
    address      varchar(200)                            not null comment '联系地址',
    `status`     tinyint unsigned                        not null comment '状态：1可用、2不可用',
    index idx_code (`code`),
    index idx_type (`type`),
    index idx_status (`status`),
    unique unq_code (`code`)
) comment ='供货商表';

# 初始化数据
INSERT INTO t_supplier (id, code, name, type, link_man, tel, bank_name, bank_account, address, status) VALUES (1, '2394125', 'A供货商', 1, '李强', '13399999999', '', null, '辽宁省高兴区121号', 1);

供货商关联商品表：复合主键（供货商ID、商品ID）

# 创建表
create table t_supplier_sku
(
    supplier_id int unsigned not null comment '供货商ID',
    sku_id      int unsigned not null comment '商品ID',
    primary key (supplier_id, sku_id)
) comment ='供货商与商品关联表';

# 初始化数据
INSERT INTO t_supplier_sku (supplier_id, sku_id) VALUES (1, 1);
INSERT INTO t_supplier_sku (supplier_id, sku_id) VALUES (1, 2);
INSERT INTO t_supplier_sku (supplier_id, sku_id) VALUES (1, 3);

12.【采购表】、【入库表】设计

商品采购和入库流程分析

挑选商品：采购员，通过进销存模块选好供货商、商品。数据库中记录下采购的数据
通知供货商：系统通过邮件或微信的方式通知供货商
供货商送货：供货商接到通知后，进行送货
验货：保管员检验货物之后进行入库处理
入库：并填写入库单，存储在数据库中

采购表：主键ID、采购商品ID、采购数量、指定仓库、采购价格、建议零售价、采购员工ID、采购状态、创建时间
- sku_id：采购商品ID
- num：采购的数量
- warehouse_id：为哪一个仓库采购商品
- in_price：采购商品价格
- out_price：该商品零售价建议多少钱
- buyer_id：员工表ID，谁采购的
- status：状态
- create_time：创建时间

# 创建表
create table t_purchase
(
    id           int unsigned primary key auto_increment not null comment '主键',
    sku_id       int unsigned                            not null comment '商品ID',
    num          int unsigned                            not null comment '数量',
    warehouse_id int unsigned                            not null comment '仓库ID',
    in_price     decimal(10, 2) unsigned                 not null comment '采购价格',
    out_price    decimal(10, 2) unsigned comment '建议零售价',
    buyer_id     int unsigned                            not null comment '采购员员工ID',
    `status`     tinyint unsigned                        not null comment '状态：1未完成、2已完成',
    create_time  timestamp default now()                 not null comment '添加时间',

    index idx_sku_id (sku_id),
    index idx_warehouse_id (warehouse_id),
    index idx_buyer_id (buyer_id),
    index idx_status (`status`),
    index idx_create_time (create_time)
) comment ='采购表';

# 初始化数据
INSERT INTO t_purchase (id, sku_id, num, warehouse_id, in_price, out_price, buyer_id, status, create_time) VALUES (1, 1, 50, 1, 3000.00, 3299.00, 20, 1, '2020-05-20 09:47:05');

入库表：主键ID、仓库保管员工ID、入库货物总金额、供应商ID、实际支付金额、支付方式、是否开票、备注、创建时间
- storekeeper_id：仓库保管员的员工ID
- amount：这批货物的总金额
- supplier_id：供货商 ID
- payment：这批货物的实际支付金额
  有可能是缺失的，实际付款的金额与总金额可能不一致
- payment_type：支付方式；银行卡、现金等
- invoice：是否开票
- remark：备注
- create_time：

# 创建表
create table t_productin
(
    id             int unsigned primary key auto_increment not null comment '主键',
    storekeeper_id int unsigned                            not null comment '保管员员工ID',
    amount         decimal(15, 2) unsigned                 not null comment '总金额',
    supplier_id    int unsigned                            not null comment '供应商ID',
    payment        decimal(15, 2) unsigned                 not null comment '实付金额',
    payment_type   tinyint unsigned                        not null comment '支付方式',
    invoice        boolean                                 not null comment '是否开票',
    remark         varchar(200) comment '备注',
    create_time    timestamp default now()                 not null comment '添加时间',
    index idx_storekeeper_id (storekeeper_id),
    index idx_supplier_id (supplier_id),
    index idx_payment_type (payment_type),
    index idx_create_time (create_time)
) comment ='入库信息表';

# 初始化数据
INSERT INTO t_productin (id, storekeeper_id, amount, supplier_id, payment, payment_type, invoice, remark, create_time) VALUES (1, 42, 1500000.00, 1, 150000.00, 1, 1, null, '2020-05-20 09:48:14');

采购与入库关联表：复合主键（采购ID、入库ID）

# 创建表
create table t_purchase_productin
(
    purchase_id  int unsigned not null comment '采购ID',
    productin_id int unsigned not null comment '入库ID',
    primary key (purchase_id, productin_id)
) comment ='入库商品表';

# 初始化数据
INSERT INTO t_purchase_productin (purchase_id, productin_id) VALUES (1, 1);

昵称

邮箱

网址

按正序
按倒序
按热度

MySQL-场景篇-新零售数据结构设计

预览: