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、屬性、屬性值關聯表
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 ('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

這些十二張基礎表從不同側面描述了商品資訊，其它複雜場景可以在這些基礎表上進行擴充套件。同時在第五章節我們討論了五個延伸知識，希望本文對大家有所幫助。

商品領域十二張基礎表設計思路與實現