电商数据库设计

2022-12-25

电商数据库设计

术语解释

基本信息：描述商品的信息组合，包括商品主图、商品副轮播图、商品主副标题，商品所属品牌、商品所属商家、商品描述/详情、商品属性、商品价格、商品规格、商品库存等。

基础属性：商品的基本特性，如商品的品牌、尺寸、大小、颜色等。对于所有商品包含的属性，都可以归类到基础属性中。

公共属性：指的是其他类目可以共用的属性。对于衣服而言，性别这个属性，就是公共属性。因为无论什么品牌的衣服，都具有这个属性，其属性值只有三个：男、女、中性。

销售属性：也称为规格属性，该属性是组成SKU的特殊属性，直接决定SKU的价格，直接影响到买家的购买和商家的库存管理。例如衣服的尺寸，不同尺寸的商品成本不一样，或者商家根据用户的购买场景会设置不同的定价。

非销售属性：跟SKU价格没有关系的特殊属性，属性值的改变不影响SKU’的价格。例如衣服的颜色，不同颜色的衣服价格都一样。

搜索属性：搜索商品时的特殊属性，直接影响商品的搜索结果。搜索属性与商品绑定，实现通过搜索属性查询商品。

非关键属性：关键属性、销售属性之外的其他扩展冗余属性。一般在商品配置时带 * 号的是必填的，一般是关键属性，不带 * 号的是非关键属性，不是必填的。非关键属性的目的是完善商品信息的完整性。

特殊属性：一般是用于特殊场景下的商品描述，比如带有腐蚀性的商品需要特殊包装，而“具有腐蚀性”这个标签就是一个特殊属性。在商品物流打包环节会进行特殊处理。

类目：也就是分类，为了更好地管理商品。会存在多级分类，层层关联，一对多关系往下走。类目的设计主要从两个纬度考虑，一个是前台类目，一个是后台类目。前台类目，即给前端用户看的类目。后台类目，方便商家管理商品的类目，对前端用户不一定可见。

SPU：**Standard Product Unit (标准化产品单元)**。SPU 是商品信息聚合的最小单位，是一组可复用、易检索的标准化信息的集合，该集合描述了一个产品的特性。SPU 通俗的来讲就是一类具有相同属性、属性值的商品，这些属性和属性值通常不参与商品的销售价确定。如 iphone6s 就是一个 SPU，无论你在那个平台或者实体店查询 iphone6s，他们给出的商品属性信息都是一致的。

SKU：**Stock Keeping Unit (库存量单位)**。SKU 即库存进出计量的单位，可以是以件、盒、托盘等为单位。SKU 是物理上不可分割的最小存货单元，而这个不可分割是相对于储存场景的，对于相同的商品，在不同的仓储场景下，对 SKU 的管理是不一样的。如去超市买早餐奶，可以买一袋，也可以买一箱。按最小单元来说，那么” 袋” 就是超市管理早餐奶的 SKU。超市的货源是来自供应商的，供应商是按箱卖给超市的，所以” 箱” 是供应商管理早餐奶的 SKU。SKU 可以简单理解为：SKU = SPU + 销售属性，当在 SPU 上添加上商家、颜色、内存等影响销售加价的属性后，这个商品就成一个 SKU。所以当想要确定一个 SKU 时，首先需要明确有几个属性在影响价格，找到对应的属性，列出每个属性的属性值，通过属性值的组合就能确定所有的 SKU。大多数情况下，一旦确定了组成商品的 SKU 销售属性后，就不能再对销售属性进行修改，如果属性值错误，就删除重来。

库存：对于 SKU 的出售自然就涉及到库存，在商品维护界面仅有一个对库存数维护的地方，也就是实际可售库存。对于大平台的入驻商户来说，通常采用手动录入方式 (有开发能力的可以做系统对接)，让商户自己维护 SKU 的销售数量。具体填写多少由商户自己决定，这个填写的数字就是实际可售库存。而对于平台自营来说，公司通常都有自己的仓储系统，每个 SKU 都有明确的存储记录，并且部分 SKU 参与内部任务 (如调拨、拍照、战略储存等) 使得当前时间不可售。因此实际的 SKU 库存可能并不等于全部可售，具体实际可售库存需要通过仓储系统经过统计同步到商户模块中，而不是由买手自己手动维护。

上架 / 下架：除了在商品的基础属性上设置有商品的上架 / 下架操作，通过在具体的 SKU 上也设置一个上架 / 下架操作，可以更加细粒度的管理到具体的 SKU 上下架状态。但是，如果SKU选择是根据规格参数列表组合实现筛选的，而不是根据SKU列表选择，最好不设置SKU的上下架功能，因为SKU下架，规则参数的组合就会查询不到SKU。如果一定要设置SKU上下架，需要设置SKU为空的模板统一返回。没有上下架功能，也可以实现用户不能购买，把SKU的库存设置为0即可。

第三方编码：平台在生成 SKU 时，会为每个 SKU 也分配相应的拣货码 (可能是商品身上的条码，也可能是公司内部自己定义的编码)，以方便拣货时使用。如果是自营平台，买手采购时供应商会提供给采购平台以便录入系统；如果是平台商户，一是平台没有办法采集数据，二是各商户各自在拣货时使用的规则各不相同，所以仅给一个可以让商户自己维护的字段。当有订单产生时，在订单模块中导出的拣货单中会带有维护的第三发编码以供商户进行拣货操作。

SPEC：specificatons，规格参数模板，json 格式。记录SPU的所有规格参数。建议只记录销售属性的所有规格参数，非销售属性的规格参数使用PARAM记录。

笛卡尔积：笛卡尔乘积是指在数学中，两个集合 X 和 Y 的笛卡尔积（Cartesian product），又称直积，表示为 X × Y，第一个对象是 X 的成员而第二个对象是 Y 的所有可能有序对的其中一个成员。通俗理解就是一个集合中的所有元素与另外一个集合中的所有元素的所有组合。需要注意有先后顺序。如集合 A={a,b}, B={0,1,2}，则A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

单品：一个商品就是一个独立的商品，不包含关联的子商品。

复合商品：一个商品包含多个关联子商品，支持打包出售的模式。

数据库设计

类目

支持多级类目，类目可设置是否显示，是否为导航菜单，排序等。类目的层级通过上级类目id实现，第一级设置为0，第二级设置为第一级的id，依次类推。类目的结构通过parent_id查询并正反推获得。

create table sp_category
(
    id          int(19) auto_increment comment '主键自增id'
        primary key,
    name        varchar(90) default '' not null comment '类目名称',
    is_show     tinyint(2)  default 0  not null comment '是否显示;0-不显示，1-显示',
    is_menu     tinyint(2)  default 0  not null comment '是否导航;0-不为导航菜单，1-导航菜单',
    sort_num    int(19)     default 0  not null comment '排序',
    parent_id   int(19)     default 0  not null comment '上级类目id',
    create_time datetime               null comment '创建时间',
    update_time datetime               null comment '更新时间',
    del         tinyint(2)  default 0  not null comment '是否删除;0-未删除，1-删除'
)
    comment '类目表' charset = utf8mb4;

品牌

品牌不是商家，品牌就是品牌，品牌和商家是多对多的关系，一个品牌可以有多个商家，一个商家可以经营多个品牌。

品牌需要用户端展示，作为筛选条件，展示为顶部筛选栏。

create table sp_brand
(
    id                 int(19) auto_increment comment '自增主键id'
        primary key,
    brand_name         varchar(90)  default '' not null comment '品牌名称',
    main_image         varchar(90)  default '' not null comment '品牌封面',
    brand_introduction varchar(900) default '' not null comment '品牌简介',
    brand_initial      char(2)      default '' not null comment '品牌首字母',
    sort_num           int          default 0  not null comment '排序;权重越大越靠前',
    create_time        datetime                null comment '创建时间',
    update_time        datetime                null comment '更新时间',
    del                tinyint(2)   default 0  not null comment '是否删除;0-未删除，1-删除'
)
    comment '品牌表' charset = utf8mb4;

商家

后台使用若依前后端分离项目实现，主要因为若依框架有完善的账户权限管理，方便管理B端商家。

设计中把若依系统的用户id和商家id一对一绑定，实现若依系统用户代理商家管理后台。

商家登录若依用户，管理本商家的所有数据。

create table sp_brand_merchant
(
    id                    int(19) auto_increment comment '自增主键id'
        primary key,
    ruoyi_user_id         int(19)      default 0  not null comment '若依用户ID',
    merchant_name         varchar(90)  default '' not null comment '商家名称',
    merchant_introduction varchar(900) default '' not null comment '商家简介',
    merchant_images       varchar(600) default '' not null comment '商家图片;多张图片逗号分割',
    sort_num              int          default 0  not null comment '排序;权重越大越靠前',
    create_time           datetime                null comment '创建时间',
    update_time           datetime                null comment '更新时间',
    del                   tinyint(2)   default 0  not null comment '是否删除;0-未删除，1-删除'
)
    comment '商家（品牌）表' charset = utf8mb4;

类目-品牌

类目与品牌是多对多关系，必须设计中间表实现关系。

商家与类目不关联，商家与品牌也不关联，商家上架商品须使用类目和品牌，须指定商品的类目和品牌。

也就意味着，不能直接通过类目查询商家，也不能直接通过品牌查询商家，但是通过类目和品牌筛选出商品后，可以直接根据商品查询商家信息。

create table sp_category_brand
(
    id          int(19) auto_increment comment '自增主键id'
        primary key,
    category_id int default 0 not null comment '分类ID',
    brand_id    int default 0 not null comment '品牌ID'
)
    comment '类目、品牌关联表';

商家地址

当设计退换货时，需要显示商家地址信息，用户才能寄出退换货。

create table sp_merchant_address
(
    id          int(19) auto_increment comment '自增主键id'
        primary key,
    merchant_id int(19)      default 0  not null comment '商家id',
    contact     varchar(60)  default '' not null comment '联系人;称呼',
    phone       varchar(15)  default '' not null comment '手机号',
    province    varchar(60)  default '' not null comment '省份',
    city        varchar(60)  default '' not null comment '市',
    area        varchar(90)  default '' not null comment '县/区',
    address     varchar(150) default '' not null comment '详细地址',
    is_default  tinyint(2)   default 0  not null comment '是否是默认地址;0-不是，1-是',
    alias       varchar(90)  default '' not null comment '地址别名',
    create_time datetime                null comment '创建时间',
    update_time datetime                null comment '更新时间',
    del         tinyint(2)   default 0  not null comment '是否删除;0-未删除，1-删除'
)
    comment '商家收货地址表' charset = utf8mb4;

用户收货地址

用户收货地址的基本信息

address_keyword是使用腾讯地图的关键词搜索需要的

create table sp_user_address
(
    id              int(19) auto_increment comment '自增主键id'
        primary key,
    user_id         varchar(40)  default '' not null comment '用户id',
    contact         varchar(60)  default '' not null comment '联系人;称呼',
    phone           varchar(15)  default '' not null comment '手机号',
    province        varchar(60)  default '' not null comment '省份',
    city            varchar(60)  default '' not null comment '市',
    area            varchar(90)  default '' not null comment '县/区',
    address         varchar(150) default '' not null comment '详细地址',
    is_default      tinyint(2)   default 0  not null comment '是否是默认地址;0-不是，1-是',
    alias           varchar(90)  default '' not null comment '地址别名',
    address_keyword varchar(90)  default '' not null comment '地址关键词title',
    create_time     datetime                null comment '创建时间',
    update_time     datetime                null comment '更新时间',
    del             tinyint(2)   default 0  not null comment '是否删除;0-未删除，1-删除'
)
    comment '用户收货地址表' charset = utf8mb4;

商品管理

包括规格参数管理，SPU管理，SKU管理

SPEC

不同的商家都有自己的SPEC，SPEC还与类目关联起来。

负责记录商家关于类目、SPU的属性选项。

create table sp_spec
(
    id          int(19) auto_increment comment '主键自增id'
        primary key,
    merchant_id int(19)     default 0  not null comment '商家id',
    category_id int(19)     default 0  not null comment '类目编号id',
    name        varchar(90) default '' not null comment '名称',
    options     varchar(120)           not null comment '规格选项;逗号分割',
    sort_num    int(19)     default 0  not null comment '排序;权重越大越靠前',
    create_time datetime               null comment '创建时间',
    update_time datetime               null comment '更新时间',
    del         tinyint(2)  default 0  not null comment '是否删除;0-未删除，1-删除'
)
    comment 'SPEC选项表' charset = utf8mb4;

SPU

聚合商品信息模板，SKU的公共信息大多应该放在SPU。

绑定类目和品牌id，实现通过类目和品牌筛选。

spec_items：销售属性组合

para_items：非销售属性组合

SKU的生成建议只使用spec_items的参数列表

para_items共用，因为不影响价格，不建议增加到SKU中，通过SPU查询返回属性选项，记录到订单即可。

sale_num：SKU合计销量

enable_spec：如果启用规格的话，SKU根据spec_items的笛卡尔组合实现；不启用规格的话，SKU不使用销售属性组合，只生成一个SKU，这个SKU的规格选项由para_items决定。

create table sp_spu
(
    id                int(19) auto_increment comment '主键自增id'
        primary key,
    merchant_id       int(19)        default 0    not null comment '商家id',
    category_id       int(19)        default 0    not null comment '类目id',
    brand_id          int(19)        default 0    not null comment '品牌id',
    spu_id            varchar(40)    default ''   not null comment 'SPU_ID;雪花id',
    spu_name          varchar(200)                not null comment 'SPU名称',
    caption           varchar(180)   default ''   not null comment '副标题',
    main_image        varchar(80)    default ''   not null comment '主图片',
    images            varchar(2000)               not null comment '图片列表;多图片逗号分割',
    introduction      varchar(2000)               not null comment '介绍',
    spec_items        varchar(600)   default ''   not null comment '规格列表;JSON数组字符串',
    para_items        varchar(600)   default ''   not null comment '参数列表;JSON数组字符串',
    sale_num          int            default 0    not null comment '总销量',
    status            tinyint(2)     default 0    not null comment '审核状态;0-未审核，1-通过，2-驳回',
    is_marketable     tinyint(2)     default 0    not null comment '是否上架;0-未上架，1-上架',
    enable_spec       tinyint(2)     default 0    not null comment '启用规格;0-未启用，1-启用',
    min_price         decimal(10, 2) default 0.00 not null comment 'SKU最低价格',
    order_num         int(19)        default 0    not null comment '排序，值越大越靠前',
    create_time       datetime                    null comment '创建时间',
    update_time       datetime                    null comment '更新时间',
    del               tinyint(2)     default 0    not null comment '是否删除;0-未删除，1-删除',
)
    comment 'SPU单元表' charset = utf8mb4;

SKU

SKU由SPU+spec_items生成，使用spec_items参数笛卡尔组合生成每个SKU。

SKU的属性组合由两部分组成，一部分是SKU的spec，一部分是SPU的para_items。一者为销售属性，增加到SKU中；一者为非销售属性，不增加带SKU，记录在SPU。

一般都是直接返回SKU列表供用户选择，SKU名称会包含规格属性。

如果返回的是SPU的spec_items，就要通过匹配JSON对象返回特定的SKU，使用Gson实现，如下：

（SPU详情显示规格参数选项，用户选择不同的规格组合，提交规格组合的JSON字符串及SPUID到后端查询对应的SKU，后端先查询SPU下的所有SKU，然后再根据规格组合的JSON字符串筛选出特定SKU。）

create table sp_sku
(
    id                int(19) auto_increment comment '主键自增id'
        primary key,
    spu_id            varchar(40)    default ''   not null comment 'SPU_ID',
    sku_id            varchar(40)    default ''   not null comment 'SKU_ID;雪花id',
    sku_name          varchar(90)    default ''   not null comment 'SKU名称',
    main_image        varchar(80)    default ''   not null comment '商品主图片',
    images            varchar(2000)               not null comment '商品图片列表;逗号分割',
    spec              varchar(300)   default ''   not null comment '规格;JSON字符串',
    price             decimal(10, 2) default 0.00 not null comment '价格;单位：分',
    official_price    decimal(10, 2) default 0.00 not null comment '官方价，原价',
    weight            decimal(24, 2) default 0.00 not null comment '重量;单位：克',
    stock             int            default 0    not null comment '库存数量',
    sale_num          int            default 0    not null comment '销量',
    status            tinyint(2)     default 1    not null comment '商品状态;0-下架，1-正常，2-删除',
    create_time       datetime                    null comment '创建时间',
    update_time       datetime                    null comment '更新时间',
    del               tinyint(2)     default 0    not null comment '是否删除;0-未删除，1-删除'
)
    comment 'SKU单位表' charset = utf8mb4;

购买订单

用户购买商品，生成购买订单，包括父子订单两部分，一个父订单至少包含一个子订单，父订单与子订单是一对多关系。

这种父子订单结构，可以实现购物车支付，也支持单笔支付。

父订单

父订单包含当前订单的主要信息，如：商品数量、总价格、支付方式、订单状态等，不包括商品相关的详细信息。

create table sp_buy_order
(
    id             int(19) auto_increment comment '主键自增id'
        primary key,
    user_id        varchar(40)    default ''   not null comment '用户id',
    order_id       varchar(40)    default ''   not null comment '订单id;雪花id',
    total_num      int            default 0    not null comment '数量合计',
    total_money    decimal(24, 2) default 0.00 not null comment '金额合计',
    pre_money      decimal(24, 2) default 0.00 not null comment '优惠金额',
    post_fee       decimal(24, 2) default 0.00 not null comment '运费',
    pay_money      decimal(24, 2) default 0.00 not null comment '实付金额',
    transaction_id varchar(40)    default ''   not null comment '交易流水号，易宝，支付宝',
    status         tinyint(2)     default 0    not null comment '订单状态;0-待支付，1-已支付，2-已取消',
    pay_type       tinyint(2)     default 0    not null comment '支付方式，0-易宝，1-E缴费，2-支付宝，3-汇潮',
    pay_time       datetime                    null comment '付款时间',
    end_time       datetime                    null comment '交易完成时间',
    buyer_message  varchar(200)   default ''   not null comment '买家留言',
    create_time    datetime                    null comment '订单创建时间',
    update_time    datetime                    null comment '订单更新时间',
    del            tinyint(2)     default 0    not null comment '是否删除;0-未删除，1-删除'
)
    comment '用户购买订单表' charset = utf8mb4;

子订单

子订单关联父订单，罗列各商品的详细信息。

冗余商家id，方便直接查询这个商家的所有子订单。商家端只能通过子订单处理，因为一个父订单中会存在多个子订单，而子订单的商品可能是多个商家的，所以商家后台只能通过子订单处理。

冗余SKU基本信息，减少连表查询，直接返回。

冗余SPU编号，方便直接通过id查询SPU基本信息。

confirm_receipt_ddl为了实现自动确认收货设置，通过MySQL的event事件实现。

create table sp_buy_order_item
(
    id                  int(19) auto_increment comment '主键自增id'
        primary key,
    parent_order_id     varchar(40)    default ''   not null comment '用户购买订单id',
    order_item_id       varchar(40)    default ''   not null comment '订单明细id;雪花id',
    merchant_id         int(19)        default 0    not null comment '商家id',
    user_id             varchar(40)    default ''   not null comment '用户id',
    address_id          int(19)        default 0    not null comment '收货地址id',
    sku_id              varchar(40)    default ''   not null comment 'SKU_ID',
    spu_id              varchar(40)    default ''   not null comment 'SPU_ID',
    goods_name          varchar(90)    default ''   not null comment '商品名称',
    image               varchar(80)    default ''   not null comment '商品主图片',
    price               decimal(24, 2) default 0.00 not null comment '单价',
    number              int            default 0    not null comment '数量',
    money               decimal(24, 2) default 0.00 not null comment '总金额',
    weight              decimal(24, 2) default 0.00 not null comment '重量',
    post_fee            decimal(24, 2) default 0.00 not null comment '运费',
    pay_money           decimal(24, 2) default 0.00 not null comment '实付金额',
    status              tinyint(2)     default 0    not null comment '订单状态;0-未支付，1-已支付，待发货，2-已支付，待收货，3-已签收，4-退换货中，5-已退换货，6-已取消，7-已确认收货，8-退换货驳回',
    consign_time        datetime                    null comment '发货时间',
    shipping_name       varchar(90)    default ''   not null comment '物流名称',
    shipping_code       varchar(40)    default ''   not null comment '物流单号',
    end_time            datetime                    null comment '交易完成时间',
    buyer_message       varchar(200)   default ''   not null comment '买家留言',
    is_return           tinyint(2)     default 0    not null comment '是否退换货;0-未退换货，1-退货，2-换货',
    confirm_receipt_ddl datetime                    null comment '确认收货截止时间',
    update_time         datetime                    null comment '订单更新时间',
    create_time         datetime                    null comment '订单创建时间',
    del                 tinyint(2)     default 0    not null comment '是否删除;0-未删除，1-删除'
)
    comment '用户购买订单详情表' charset = utf8mb4;

退换货订单

退换货订单的对象是购买子订单

联系人是针对C端的

confirm_receipt_ddl：在换货场景中使用，通过截止时间实现自动确认收货。

主要实现，记录退换货干系人基本信息，记录退换货的购买子订单，指定退换货订单类型，增加退换货原因和相关凭证，增加换货的物流和自动确认收货。

create table sp_return_order
(
    id                  int(19) auto_increment comment '主键自增id'
        primary key,
    user_id             varchar(40)    default ''   not null comment '用户id',
    merchant_id         int(19)        default 0    not null comment '商家id',
    buy_order_item_id   varchar(40)    default ''   not null comment '用户购买订单id;购买子订单id',
    merchant_address_id int(19)        default 0    not null comment '商家收货地址id',
    user_address_id     int(19)        default 0    not null comment '用户换货收货地址',
    order_id            varchar(40)    default ''   not null comment '退换货订单id;雪花id',
    linkman             varchar(60)    default ''   not null comment '联系人',
    linkman_mobile      varchar(13)    default ''   not null comment '联系电话',
    return_money        decimal(24, 2) default 0.00 not null comment '退款金额',
    is_return_freight   tinyint(2)     default 0    not null comment '是否退运费;0-不退，1-退',
    return_type         tinyint(2)     default 0    not null comment '退款方式;0-客服，1-支付宝，2-易宝',
    status              tinyint(2)     default 0    not null comment '申请状态;0-未审核，1-通过，2-驳回，3-完成，4-待收货，5-已签收，6-已确认收货',
    deal_time           datetime                    null comment '处理时间',
    return_cause        varchar(300)   default ''   not null comment '退换货原因',
    evidence            varchar(600)   default ''   not null comment '凭证图片',
    order_type          tinyint(2)     default 0    not null comment '订单类型;0-退货，1-换货',
    shipping_name       varchar(90)    default ''   not null comment '物流名称',
    shipping_code       varchar(40)    default ''   not null comment '物流单号',
    remark              varchar(300)   default ''   not null comment '处理备注',
    apply_time          datetime                    null comment '申请时间',
    update_time         datetime                    null comment '更新时间',
    confirm_receipt_ddl datetime                    null comment '确认收货截止时间',
    del                 tinyint(2)     default 0    not null comment '是否删除;0-未删除，1-删除'
)
    comment '用户退换货订单表' charset = utf8mb4;

ER参考图

此ER图没有经过优化，与上文可能不符，仅供参考。