1 文章概述
商品在电商领域中是一个非常重要的领域,交易行为前提是有商品信息存在。本文我们分析商品表基本设计,其它复杂场景可以在此基础上进行扩展。需要说明第一本文所用数据是测试数据,可能与真实数据有偏差,仅供演示。第二本文展示商品核心字段,一些通用字段不展示。
2 商品类目
2.1 基本信息
类目表示商品分类并且具有层级关系:
- 一级类目:图书
- 二级类目:文学
- 三级类目:小说
- 二级类目:文学
- 一级类目:电脑
- 二级类目:电脑配件
- 三级类目:显卡
- 二级类目:电脑配件
- 一级类目:生鲜
- 二级类目:水果
- 三级类目:苹果
- 二级类目:水果
2.2 三种类目
2.2.1 后台类目
后台类目有两个特点:标准和稳定。标准表示后台类目是业界通用的,并且层级不宜过多,通常不超过三级。稳定表示后台类目一旦确定不能轻易修改,否则设计上下游大量数据变更,工作量非常大,所以变更权限必须收敛到平台运营。
2.2.2 前台类目
计算机领域有一句话:任何问题都可以通过加一层解决。为了解决后台类目不能灵活调整这个问题,业界在后台类目上设计了前台类目。
运营人员通常会对后台类目进行简化和整理,更加符合用户检索习惯,前台类目可以自由关联后台类目,可以一对多、多对一或者多对多。很多电商网站PC首页展示的类目一般是前台类目。
2.2.3 店铺类目
店铺类目灵活度更好可以交由商家管理,商家可以根据自身经营策略调整店铺类目,提升交易率,一般只支持两层。综上所述我们看一个实例:
(1) 后台类目
- 一级类目:生鲜
- 二级类目:水果
- 三级类目:西瓜
- 二级类目:水果
(2) 前台类目
- 一级类目:食品/生鲜/特产
- 二级类目:新鲜水果
- 三级类目:西瓜
- 二级类目:新鲜水果
(3) 店铺类目
- 一级类目:夏日清凉
- 二级类目:甜甜大西瓜
2.3 后台类目表
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 ('10000000','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 ('10000001','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 ('10000002','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 ('10000003','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、属性、属性值关联表
- 主表:SPU基础信息
- SKU
- 主表:SKU基础信息
- 图片、spuId、价格、库存
- 关联表:SKU、属性、属性值关联表
- 主表:SKU基础信息
- SN
- 主表:SN基础信息以及与SKU关系
- 序列号、skuId
- 主表:SN基础信息以及与SKU关系
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 ('20000000', '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` DECIMAL(10,2) NOT NULL COMMENT '原价',
`discount_price` DECIMAL(10,2) 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 ('30000000', '200', '100', '1001', NULL);
insert into relation_product_sku_attribute(`relation_id`, `sku_id`, `attribute_id`, `attribute_value_id`, `custom_attribute_value`) values ('30000001', '200', '200', '2002', NULL);
insert into relation_product_sku_attribute(`relation_id`, `sku_id`, `attribute_id`, `attribute_value_id`, `custom_attribute_value`) values ('30000002', '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 ('40000000', '200', 'SN-1', 1);
insert into product_sku_sn(`sn_id`, `sku_id`, `sn`, `status`) values ('40000001', '200', 'SN-2', 1);
insert into product_sku_sn(`sn_id`, `sku_id`, `sn`, `status`) values ('40000002', '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引出商品十二张基础表:
一级类目表:category_1_background
二级类目表:category_2_background
三级类目表:category_3_background
属性表:attribute
属性值表:attribute_value
类目与属性关系表:relation_category_attribute
品牌表:product_brand
SPU表:product
SKU表:product_sku
SN表:product_sku_sn
SPU与属性关系表:relation_product_attribute
SKU与属性关系表:relation_product_sku_attribute
这些十二张基础表从不同侧面描述了商品信息,其它复杂场景可以在这些基础表上进行扩展。同时在第五章节我们讨论了五个延伸知识,希望本文对大家有所帮助。
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
机房租用,北京机房租用,IDC机房托管, http://www.fwqtg.net
相关推荐: 献给转java的c#和java程序员的数据库orm框架
献给转java的c#和java程序员的数据库orm框架 一个好的程序员不应被语言所束缚,正如我现在开源java的orm框架一样,如果您是一位转java的c#程序员,那么这个框架可以带给你起码没有那么差的业务编写和强类型体验。如果您是一位java程序员,那么该框…