1. IT分享知识网首页
  2. 未分类

商品领域十二张基础表设计思路与实现

老牧童 • • 未分类

商品领域十二张基础表设计思路与实现本文通过介绍类目 属性 品牌 SPU SKU SN 引出商品十二张基础表 一级类目表二级类目表三级类目表属性表属性值表类目与属性关系表品牌表 SPU 表 SKU 表 SN 表 SPU 与属性关系表 SKU 与属性关

大家好，欢迎来到IT知识分享网。

1 文章概述

商品在电商领域中是一个非常重要的领域，交易行为前提是有商品信息存在。本文我们分析商品表基本设计，其它复杂场景可以在此基础上进行扩展。需要说明第一本文所用数据是测试数据，可能与真实数据有偏差，仅供演示。第二本文展示商品核心字段，一些通用字段不展示。

2 商品类目

2.1 基本信息

类目表示商品分类，并且具有层级关系：

  • 一级类目：图书
  • 二级类目：文学
  • 三级类目：小说
  • 一级类目：电脑
  • 二级类目：电脑配件
  • 三级类目：显卡
  • 一级类目：生鲜
  • 二级类目：水果
  • 三级类目：苹果

2.2 三种类目

2.2.1 后台类目

后台类目有两个特点：标准和稳定。标准表示后台类目是业界通用的，并且层级不宜过多，通常不超过三级。稳定表示后台类目一旦确定不能轻易修改，否则设计上下游大量数据变更，工作量非常大，所以变更权限必须收敛到平台运营。

2.2.2 前台类目

计算机领域有一句话：任何问题都可以通过加一层解决。为了解决后台类目不能灵活调整这个问题，业界在后台类目上设计了前台类目。

运营人员通常会对后台类目进行简化和整理，更加符合用户检索习惯，前台类目可以自由关联后台类目，可以一对多、多对一或者多对多。很多电商网站 PC 首页展示的类目一般是前台类目。

2.2.3 店铺类目

店铺类目灵活度更好可以交由商家管理，商家可以根据自身经营策略调整店铺类目，提升交易率，一般只支持两层。我们看一个实例：

(1) 后台类目
  • 一级类目：生鲜
  • 二级类目：水果
  • 三级类目：西瓜
(2) 前台类目
  • 一级类目：食品 / 生鲜 / 特产
  • 二级类目：新鲜水果
  • 三级类目：西瓜
(3) 店铺类目
  • 一级类目：夏日清凉
  • 二级类目：甜甜大西瓜

2.2.4 后台类目表

CREATE TABLE `category_1_background` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `category_id` varchar(64) NOT NULL COMMENT '类目ID', `category_name` varchar(128) NOT NULL COMMENT '类目名称', PRIMARY KEY (`id`), UNIQUE KEY `uq_category_id` (`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='一级后台类目表'; CREATE TABLE `category_2_background` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `category_id` varchar(64) NOT NULL COMMENT '类目ID', `category_name` varchar(128) NOT NULL COMMENT '类目名称', `category_1_id` varchar(64) NOT NULL COMMENT '一级分类ID', PRIMARY KEY (`id`), UNIQUE KEY `uq_category_id` (`category_id`), KEY `idx_category_1_id` (`category_1_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='二级后台类目表'; CREATE TABLE `category_3_background` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `category_id` varchar(64) NOT NULL COMMENT '类目ID', `category_name` varchar(128) NOT NULL COMMENT '类目名称', `category_2_id` varchar(64) NOT NULL COMMENT '二级分类ID', PRIMARY KEY (`id`), UNIQUE KEY `uq_category_id` (`category_id`), KEY `idx_category_2_id` (`category_2_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='三级后台类目表'; insert into `category_1_background`(`category_id`,`category_name`) values ('700','电子设备'); insert into `category_2_background`(`category_id`,`category_name`,`category_1_id`) values ('800','通讯设备','700'); insert into `category_3_background`(`category_id`,`category_name`,`category_2_id`) values ('900','手机','800');

3 商品属性

3.1 属性分类

3.1.1 关键属性

关键属性是商品本质属性，关键属性中最核心两个属性是品牌、型号，一旦确定这两个属性等价于确定 SPU（Standard Product Unit）标准化管理单元，例如品牌是小米，型号是 10。在实践中这两个属性不放在属性表：

  • 型号是一种特殊属性
  • 作为 SPU 表一个字段
  • 品牌是一种特殊属性
  • 品牌信息一张独立表
  • SPU 表记录对应品牌 ID

其它关键属性例如屏幕尺寸，CPU 型号，CPU 核数还是通过属性表承载。

3.1.2 销售属性

销售属性又称为规格属性，如果关键属性确定，一旦销售属性再确定，那么就可以确定 SKU（Stock Keeping Unit）库存计量单位，可以理解为仓库中实物商品，每一个 SKU 都有一个库存数量与一个价格与之对应。电商常见销售属性有颜色、容量、版本、套餐等等。

例如关键属性品牌是小米，型号是 10，销售属性颜色是黑色和蓝色，容量是 128G 和 256G，那么共有四个 SKU：

  • 小米 10 黑色 128G
  • 小米 10 黑色 256G
  • 小米 10 蓝色 128G
  • 小米 10 蓝色 256G

3.1.3 描述属性

除了关键属性与销售属性之外，其它属性可以称为描述属性，或者非关键属性。

3.2 属性与属性值

属性和属性值由平台运营人员设置，属性有两种类型：选择类型、自定义类型。对于选择类型，运营人员需要为属性设置属性值。对于自定义类型，无需设置属性值。例如平台运营人员新增以下两个属性：

  • 颜色：选择类型，平台运营设置属性值包括黑色、蓝色、红色
  • 重量：自定义类型，无需平台运营设置属性值，在商家新增商品时根据实际情况填写

3.3 类目与属性

每个类目对应的属性是不同的，所以平台运营人员初始化属性和属性值之后，还要建立类目与属性关联关系。因为同一个属性对于不同类目重要性不同，所以在设置类目和属性关系时需要设置以下信息：

  • 属性类型：属性对于类目是关键属性、销售属性、描述属性
  • 是否必填：属性对于类目是否必填
  • 商品维度：属性对于类目是 SPU 维度还是 SKU 维度

属性还有继承关系，平台运营人员不仅可以为三级类目设置属性，还可以为一级和二级类目设置属性。例如运营人员为二级类目设置 A、B 两种属性，那么这个二级类目下三级类目同时也具有 A、B 两种属性，类目与属性关系如下图：

商品领域十二张基础表设计思路与实现

3.4 属性数据表

3.4.1 属性表

CREATE TABLE `attribute` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `attribute_id` varchar(64) NOT NULL COMMENT '属性ID', `biz_type` tinyint(1) NOT NULL COMMENT '1选择 2自定义', `attribute_name` varchar(128) NOT NULL COMMENT '属性名称', PRIMARY KEY (`id`), UNIQUE KEY `uq_attribute_id` (`attribute_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='属性表'; insert into `attribute` (`attribute_id`, `biz_type`, `attribute_name`) values('100','1','颜色'); insert into `attribute` (`attribute_id`, `biz_type`, `attribute_name`) values('200','1','存储容量'); insert into `attribute` (`attribute_id`, `biz_type`, `attribute_name`) values('300','2','重量'); insert into `attribute` (`attribute_id`, `biz_type`, `attribute_name`) values('400','2','屏幕尺寸');

3.4.2 属性值表

CREATE TABLE `attribute_value` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `attribute_value_id` varchar(64) NOT NULL COMMENT '属性值ID', `attribute_value` varchar(128) NOT NULL COMMENT '属性值名称', `attribute_id` varchar(64) NOT NULL COMMENT '属性ID', PRIMARY KEY (`id`), UNIQUE KEY `uq_attribute_value_id` (`attribute_value_id`), KEY `idx_attribute_id` (`attribute_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='属性值表'; --颜色属性值 insert into `attribute_value`(`attribute_value_id`,`attribute_value`,`attribute_id`) values ('1001','蓝色','100'); insert into `attribute_value`(`attribute_value_id`,`attribute_value`,`attribute_id`) values ('1002','黑色','100'); insert into `attribute_value`(`attribute_value_id`,`attribute_value`,`attribute_id`) values ('1003','红色','100'); --容量属性值 insert into `attribute_value`(`attribute_value_id`,`attribute_value`,`attribute_id`) values ('2001','64G','200'); insert into `attribute_value`(`attribute_value_id`,`attribute_value`,`attribute_id`) values ('2002','128G','200'); insert into `attribute_value`(`attribute_value_id`,`attribute_value`,`attribute_id`) values ('2003','256G','200');

3.4.3 属性与类目关系表

CREATE TABLE `relation_category_attribute` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `relation_id` varchar(64) NOT NULL COMMENT '关系ID', `attribute_id` varchar(64) NOT NULL COMMENT '属性ID', `category_id` varchar(64) NOT NULL COMMENT '类目ID', `category_level` tinyint(1) NOT NULL COMMENT '类目层级', `attribute_type` tinyint(1) NOT NULL COMMENT '属性类型 1关键属性 2销售属性 3描述属性', `must_fill` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否必填', `product_scope` tinyint(1) NOT NULL COMMENT '商品维度 1spu 2sku', PRIMARY KEY (`id`), UNIQUE KEY `uq_relation_id` (`relation_id`), KEY `idx_category_id_attribute_id` (`category_id`,`attribute_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='属性与类目关系表'; insert into `relation_category_attribute`(`relation_id`,`attribute_id`,`category_id`,`category_level`,`attribute_type`,`must_fill`,`product_scope`) values ('','100','900',3,2,1,2); insert into `relation_category_attribute`(`relation_id`,`attribute_id`,`category_id`,`category_level`,`attribute_type`,`must_fill`,`product_scope`) values ('','200','900',3,2,1,2); insert into `relation_category_attribute`(`relation_id`,`attribute_id`,`category_id`,`category_level`,`attribute_type`,`must_fill`,`product_scope`) values ('','300','900',3,3,1,2); insert into `relation_category_attribute`(`relation_id`,`attribute_id`,`category_id`,`category_level`,`attribute_type`,`must_fill`,`product_scope`) values ('','400','900',3,1,1,1);

3.4.4 品牌表

CREATE TABLE `product_brand` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `brand_id` varchar(128) NOT NULL COMMENT '品牌ID', `brand_cn_name` varchar(128) NOT NULL COMMENT '品牌中文名', `brand_en_name` varchar(128) NOT NULL COMMENT '品牌英文名', `logo_url` text COMMENT '品牌Logo', `brand_story` text COMMENT '品牌故事', PRIMARY KEY (`id`), KEY `idx_brand_id` (`brand_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='品牌表'; insert into `product_brand`(`brand_id`,`brand_cn_name`,`brand_en_name`) values ('1000','小米','MI');

4 商品表

4.1 基本概念

第三章节属性相关信息由平台运营人员设置，为商家维护商品信息定制一个规范，这时商家可以设置自己的商品信息。

  • SPU：标准化管理单元，商品属性最小聚合
  • SKU：库存计量单位，在仓库中实物商品，每一个 SKU 对应一个库存数量与一个价格
  • SN：序列号，在仓库中每一个实体商品

我们还是看一个手机示例：

  • SPU：小米 10
  • SKU：小米 10 黑色 128G
  • 库存 3 个
  • 单价 2000 元
  • SN：对应三个序列号
  • SN1
  • SN2
  • SN3

4.2 表设计思路

  • SPU
  • 主表：SPU 基础信息
  • 图片、品牌、型号、类目
  • 关联表：SPU、属性、属性值关联表
  • SKU
  • 主表：SKU 基础信息
  • 图片、spuId、价格、库存
  • 关联表：SKU、属性、属性值关联表
  • SN
  • 主表：SN 基础信息以及与 SKU 关系
  • 序列号、skuId

4.3 商品表

4.3.1 SPU 主表

CREATE TABLE `product` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `product_id` varchar(64) NOT NULL COMMENT '商品id', `shop_id` varchar(64) NOT NULL COMMENT '店铺id', `brand_id` bigint(20) NOT NULL COMMENT '品牌id', `product_model` varchar(256) NOT NULL COMMENT '商品型号', `product_name` varchar(256) NOT NULL COMMENT '商品名称', `sale_status` tinyint(1) NOT NULL COMMENT '销售状态 1上架 2下架', `category_3_id` varchar(64) NOT NULL COMMENT '三级分类id', `img_url` text COMMENT '图片路径', `description` text COMMENT '商品描述', PRIMARY KEY (`id`), UNIQUE KEY `uq_product_id` (`product_id`), KEY `idx_brand_id` (`brand_id`), KEY `idx_category_3_id` (`category_3_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='SPU'; insert into product(`product_id`, `shop_id`, `brand_id`, `product_model`, `product_name`, `category_3_id`, `sale_status`) values ('100', 'shop_1','100', '10', '小米10手机', '900', 1);

4.3.2 SPU 与属性关系表

CREATE TABLE `relation_product_attribute` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `relation_id` varchar(64) NOT NULL COMMENT '关系ID', `product_id` varchar(64) DEFAULT NULL COMMENT '商品ID', `attribute_id` varchar(64) NOT NULL COMMENT '属性ID', `attribute_value_id` varchar(64) DEFAULT NULL COMMENT '属性值ID', `custom_attribute_value` varchar(256) DEFAULT NULL COMMENT '自定义属性值', PRIMARY KEY (`id`), UNIQUE KEY `uq_relation_id` (`relation_id`), KEY `idx_product_id` (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='SPU与属性关系表'; insert into relation_product_attribute(`relation_id`, `product_id`, `attribute_id`, `attribute_value_id`, `custom_attribute_value`) values ('', '100', '400', NULL, '6.67英寸');

4.3.3 SKU 主表

CREATE TABLE `product_sku` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `sku_id` VARCHAR(64) NOT NULL COMMENT 'skuId', `sku_name` VARCHAR(128) NOT NULL COMMENT 'sku名称', `product_id` VARCHAR(64) NOT NULL COMMENT '商品id', `sale_status` tinyint(1) NOT NULL COMMENT '销售状态 1上架 2下架', `orgin_price` DOUBLE NOT NULL COMMENT '原价', `discount_price` DOUBLE NOT NULL COMMENT '优惠价格', `stock_count` INT(11) NOT NULL COMMENT '剩余库存', `lock_stock_count` INT(11) NOT NULL COMMENT '锁定库存', `sale_stock_count` INT(11) NOT NULL COMMENT '销售量', `sku_img_url` TEXT COMMENT '图片路径', `sku_description` TEXT COMMENT '商品描述', PRIMARY KEY (`id`), UNIQUE KEY `uq_sku_id` (`sku_id`), KEY `idx_product_id` (`product_id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='SKU'; insert into `product_sku`(`sku_id`,`sku_name`,`product_id`,`orgin_price`,`discount_price`,`stock_count`,`lock_stock_count`,`sale_stock_count`,`sku_img_url`,`sku_description`, `sale_status`) values ('200','小米 10 蓝色 128G', '100',3000,3000,3,1,1000,NULL,NULL, 1);

4.3.4 SKU 与属性关系表

CREATE TABLE `relation_product_sku_attribute` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `relation_id` varchar(64) NOT NULL COMMENT '关系ID', `sku_id` varchar(64) DEFAULT NULL COMMENT 'skuId', `attribute_id` varchar(64) NOT NULL COMMENT '属性ID', `attribute_value_id` varchar(64) DEFAULT NULL COMMENT '属性值ID', `custom_attribute_value` varchar(256) DEFAULT NULL COMMENT '自定义属性值', PRIMARY KEY (`id`), UNIQUE KEY `uq_relation_id` (`relation_id`), KEY `idx_sku_id` (`sku_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='SKU与属性关系表'; insert into relation_product_sku_attribute(`relation_id`, `sku_id`, `attribute_id`, `attribute_value_id`, `custom_attribute_value`) values ('', '200', '100', '1001', NULL); insert into relation_product_sku_attribute(`relation_id`, `sku_id`, `attribute_id`, `attribute_value_id`, `custom_attribute_value`) values ('', '200', '200', '2002', NULL); insert into relation_product_sku_attribute(`relation_id`, `sku_id`, `attribute_id`, `attribute_value_id`, `custom_attribute_value`) values ('', '200', '300', NULL, '173克');

4.3.5 SN 表

CREATE TABLE `product_sku_sn` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', `sn_id` varchar(64) NOT NULL COMMENT '序列号ID', `sku_id` varchar(64) DEFAULT NULL COMMENT 'skuId', `sn` varchar(64) DEFAULT NULL COMMENT '序列号', `status` tinyint(1) NOT NULL COMMENT '状态 1未售 2已售', PRIMARY KEY (`id`), UNIQUE KEY `uq_sn_id` (`sn_id`), UNIQUE KEY `uq_sn_sku` (`sku_id`,`sn`), KEY `idx_sku_id` (`sku_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='SN'; insert into product_sku_sn(`sn_id`, `sku_id`, `sn`, `status`) values ('', '200', 'SN-1', 1); insert into product_sku_sn(`sn_id`, `sku_id`, `sn`, `status`) values ('', '200', 'SN-2', 1); insert into product_sku_sn(`sn_id`, `sku_id`, `sn`, `status`) values ('', '200', 'SN-3', 1);

5 延伸知识

5.1 涉及角色

商品操作总体上分为平台运营和商家两个角色，平台运营主要维护类目、属性、类目与属性关系信息，相当于为商家维护商品设置规范。商家主要维护 spu、sku、spu 具体属性值是什么、sku 具体属性值是什么、上下架状态。

5.2 自增主键不赋予业务含义

以商品表为例，我们不应该以id作为商品 Id，应该设置product_id作为商品 Id，并且要求product_id全局唯一，因为这样便于当数据量过大时进行分库分表。

5.3 允许适度冗余

商品表是一个高读写比典型，可能看 10 次才会产生 1 次购买行为，所以如果可以一次查询就查出所需信息对性能会更友好。

本文表设计并没有进行冗余，例如如果要冗余可以在relation_product_sku_attribute表中新增attribute_value字段记录属性值。冗余问题就是数据一致性，例如当属性值发生变化时，上述字段也要同步进行修改。

5.4 合理使用 ES

正如上述章节所述，商品表是一个高读写比的典型，我们希望一次查询可以将所需信息查询出来，而不是跨多张表去查询，但是我们又不想在业务表冗余数据。

我们可以将商品信息平铺到 ES 中一个索引，这个索引具有商品全部字段信息，例如在查询商品列表或者商详时可以直接访问这个索引。

5.5 库存独立成表

product_sku有库存字段，对于库存字段修改相对而言比较多，其它信息变更比较少，因为如果出现下单、购买、退款行为，库存信息就会发生变化。所以可以将 sku 主表库存字段单独成表，从而减轻主表压力。

6 文章总结

本文通过介绍类目、属性、品牌、SPU、SKU、SN 引出商品十二张基础表：

  • 一级类目表
  • 二级类目表
  • 三级类目表
  • 属性表
  • 属性值表
  • 类目与属性关系表
  • 品牌表
  • SPU 表
  • SKU 表
  • SN 表
  • SPU 与属性关系表
  • SKU 与属性关系表

这些十二张基础表从不同侧面描述了商品信息，其它复杂场景可以在这些基础表上进行扩展。同时在第五章节我们讨论了五个延伸知识，希望本文对大家有所帮助。

免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://haidsoft.com/140616.html

赞 (0)
老牧童老牧童
0
上一篇 2025-05-25 17:00
下一篇 2025-05-25 17:10

相关推荐

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

关注微信