数据库技术提升-MySQL数据库原理、设计与应用【1.9】

4.4.4 商品表

商品分类与商品是一对多的联系,一个分类中有多件商品。商品表的具体结构如表 4-14所示。

在表 4-14 中设计的商品表 sh_goods 共有 21个字段,具体说明如下商品 id:主键,使用无符号整型存储,自动增长。。
·分类 id:商品所属分类,使用无符号整型存储,自动增长。
SPU id:标准化产品单元 id(具体会在后面讲解)。
编号:长度在 20个字符以内。。
·名称:长度在 120个字符以内。
·关键词:用于在搜索和浏览列表时更准确地找到该商品。
图片:用于在商品列表中显示商品的预览图,保存图片路径。。
提示:用于醒目地显示该商品的提示信息,如促销信息、推荐语等
描述:用于通过简短的文字描述商品的基本信息，
·详情:用于描述商品的详细信息。

评分:用户购买商品后的评分(1~5),此处为平均分，主要用于排序。
·价格、库存:商品的价格和库存。
是否上架、是否删除、是否包邮:0 表示是,1 表示否。
销量计数、评论计数:商品的销量和评论数量,主要用于排序。
·上架时间:商品的上架时间,主要用于排序。
创建时间:该商品被插入时的时间,后台使用。。
·更新时间:该商品最后一次修改的时间,后台使用。小提示:
(1)对于商品编号,若没有特殊要求,一般情况下使用商品 id 作为编号。但考虑到将来有可能更换另外一套规则来管理编号,因此这里预留了一个编号字段。
(2)由于商品分类是多级嵌套结构,一般来说,商品所属的分类应该是叶子节点的分类。如果现有分类不能满足需求,可以增加一个名称为“其他”的分类。

(3)用户上传的图片等文件,一般是由 Web 服务器来管理的,数据库中只保存它们的引用路径。
下面根据表 4-14 所示的表结构,创建商品表,对应的 SQL 语句如下

CREATE TABLE sh goods(
id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT '商品 id'
Category id INT UNSIGNED NOT NULL DEFAULT O COMMENT '分类id',
Spu id INT UNSIGNED NOT NULL DEFAULT O COMMENT 'SPU id',Sn VARCHAR(20)NOT NULL DEFAULT"COMMENT'编号'
name VARCHAR(120)NOT NULL DEFAULT"COMMENT'名称'
keyword VARCHAR (255)NOT NULL DEFAULT ''COMMENT '关键词'
picture VARCHAR(255)NOT NULL DEFAULT"'COMMENT图片'
tipS VARCHAR (255)NOT NULL DEFAULT"COMMENT'提示'description VARCHAR (255)NOT NULL DEFAULT "'COMMENT '描述',
Content TEXT NOT NULLCOMMENT"详情:price DECIMAL (10,2)UNSIGNED NOT NULL DEFAULT 0 COMMENT '价格'，StOck INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '库存”
SCOre DECIMAL(3,2)UNSIGNED NOT NULL DEFAULT 0 COMMENT '评分'
is On Sale TINYINT UNSIGNED NOT NULL DEEAULT 0 COMMENT'是否上架'
iS del TINYINT UNSIGNED NOT NULL DEFAULT O COMMENT'是否删除',
is free shipping TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否包邮'
se11 Count INT UNSIGNED NOT NULL DEFAULT 0 COMMENT'销量计数'
Comment COUnt INT UNSIGNED NOT NULL DEFAULT 0 COMMENT "评论计数'
on sale time INT DATETIME DEFAULT NULL COMMENT'上架时间'
Create time DATETIME NOT NULL DEFAULT CURRENT TIMESTAMP COMMENT '创建时间'update time DATETIME DEFAULT NULL COMMENT 更新时间')ENGINE= InnODB DEEAULT CHARSET-utf8:
创建商品表后,插人测试数据,具体 SQL 语句如下
INSERT INTo sh goods (id, category id, name, keyword, content, price.
stock,score,coment count)VALUES
(1,3，'2B铅笔'，"文具"，"考试专用'，0.5，500，4.9，40000)，
(2，3，"钢笔'，"文具"，"练字必不可少'，15，300，3.9，500),

(3，3，'碳素笔"，"文具’，"平时使用'，1，500，5，98000)，
(4，12，"超薄笔记本"，"电子产品'，"轻小便携'，5999，0，2.5，200)，
(5,6，"智能手机'，"电子产品’，人人必备'，1999，0，5，98000)，
(6,8，'桌面音箱'，电子产品'，扩音装备'，69，750，4.5，1000),
(7,9，'头戴耳机'，"电子产品'，"独享个人世界'，109，0，3.9，500),
(8，10，'办公电脑"，"电子产品""适合办公'，2000，0，4.8，6000)，
(9,15，"收腰风衣'，"服装'，·春节潮流单品'，299，0，4.9，40000),
(10，16，"薄毛衣"，"服装"，"居家旅行必备"，48，0，4.8，98000);

4.4.5 商品规格表

        在电子商务网站中有 SPU(Standard Product Unit,标准化产品单元)和 SKU(StockKeeping Unit,库存量单位)的概念。它们可以简单理解为看待商品的两种不同的角度。SPU 从信息聚合的角度看待商品,SKU从库存管理的角度看待商品。例如,一款饮料有 500ml和 750ml两种规格,对应的库存和价格不同,它们是不同的SKU,在 sh_goods 表中每个 SKU 商品是一条商品记录。在店铺中展示商品时,如果按照SKU 进行展示,会出现大量冗余的信息(同一款饮料因为规格不同重复出现),不利于顾客挑选商品,因此店铺通常会将相同款式的商品合并成 SPU,如图 4-6 所示。


        在图 4-6 中,整个页面可以理解为一件 SPU 商品,在页面中可以切换 500ml 和 750ml这两种规格的 SKU商品,当切换后,价格和库存也会发生变化。
        需要注意的是,图 4-6演示的商品规格只有一个维度,即容量,而有些商品的规格可能有多个维度,如手机的规格可以分为网络、颜色、内存3个维度,如图 4-7 所示。
        由此可见,不同商品的规格维度不固定,不同维度的规格项的数量也不固定。为了满足这种需求,一般需要如下4张表才能够实现。

        在上述表中,sh goods_spu 保存 SPU 的 id 和名称,一个 SPU 包含多件商品,sh_goods中的 spu_id 字段就表示该商品所属的 SPU;sh_goods_spec 保存规格,如“网络、颜色、内存”;sh_goods_spec item 保存规格项,如“移动、电信”,一个规格包含多个规格项;sh_goodsspec_set 保存商品的规格项组合,如“电信-白色-4GB”(实际保存的是 id)。接下来通过表 4-15~表 4-18 展示上述 4 张表的表结构。

下面根据表 4-15~表 4-18 所示的表结构,创建上述4张表,对应的 SQL语句如下

#商品 SPU表
CREATE TABLE sh goods spu(
id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT 'SPU id',name VARCHAR(80)NOT NULL DEFAULT "'COMMENT 'SPU 名称')ENGINE-InnODB DEFAULT CHARSET-utf8;
#商品规格表
CREATE TABLE sh goods spec(
id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT '规格 id',name VARCHAR(80)NOT NULL DEFAULT'COMMENT'规格名称')ENGINE-InnODB DEFAULT CHARSET-utf8;
#商品规格项表
CREATE TABLE sh goods spec item(
id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT '规格硕 id'Spec id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '规格 id',name VARCHAR(80)NOT NULL DEFAULT"COMMENT名称'description VARCHAR (255)NOT NULL DEFAULT "'COMMENT '描述'picture VARCHAR (255)NOT NULL DEFAULT ''COMMENT '可选图'ENGINE- InnODB DEFAULT CHARSET-utf8;
#商品规格组合表
CREATE TABLE sh goods spec set(
goods id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'SKU id',spec item id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '规格 id')ENGINE-InnODB DEFAULT CHARSET-utf8;
数据表创建完成后,为上述4张表添加测试数据,具体 SQL 语句如下
INSERT INTo sh goods spu(id,name) VALUES
(1，'新款智能手机');
INSERT INTo sh goods spec (id,name) VALUES
(1，"网络')，(2，"颜色')，(3，'内存');
INSERT INTO sh goods spec item (id, spec id, name) VALUES
(1,1，'移动')，(2，1，'电信')，(3，2,'白色'),
(4,2，'黑色')，(5，3,'4GB')，(6，3,'6GB');
INSERT INTo sh goods spec set (goods id, spec item id) VALUES
(5,2)，(5，3)，(5，5);

        在上述测试数据中,goods_id为5的商品(智能手机)的规格项有“电信”“白色”和“4GB”,表示该商品的规格为“网络:电信,颜色:白色,内存:4GB”,该商品和其他规格的商品组成了一个 SPU,即 sh_goods_spu 表中 id为1的“新款智能手机”。
        需要注意的是,在查询商品列表时,如果直接査询 sh_goods 表中的记录,会导致一个SPU 中的多个 SKU 商品都显示在列表中,占用列表的空间,不利于挑选商品。为了解决这个问题,可以按照表中的 spu id字段去除重复记录,只保留一条商品记录即可。
        小提示:在实际应用中,考虑到多张表的查询操作过于复杂,有时会通过逆规范化的设计增加一些冗余字段以方便查询。例如,在 sh_goods_spu 表中增加一个字段,将每个 SPU相关的规格、规格项、规格组合以 JSON 格式编码后保存,从而简化查询。

4.4.6 商品属性表
商品属性主要有两个作用,一个是在商品展示页面中显示商品属性表格,一个是在商品列表页面中筛选商品,分别如图 4-8和图 4-9 所示。

需要注意的是,图 4-8和图 4-9中的属性是针对手机分类的商品设计的,而本项目有多种商品分类,应为不同分类的商品设计不同的属性模板
为了满足上述需求，可以通过如下4张表来实现

sh goods attr(id, parent id, category id,name, sort)
sh goods attr value(id, goods id, attr id, attr value)
sh goods selector (id, parent id, category id, name, sort)
sh goods selector value (id, goods id, selector id, selector value)

        在上述表中,sh_goods_attr 保存商品属性,一般有两个层级,第1级如“主体、基本信息、主芯片”,第 2级如“上市年份、上市月份、机身颜色”;sh_goods_attr_value 保存商品的属性值;sh_goods_selector 保存筛选条件,一般有 2~3级,2级如“机身内存-8GB 以下”3 级如“高级选项-网络-电信”;sh_goods_selector_value 保存商品的筛选值。

        严格来说,sh_goods_attr 和 sh_goods_selector 表中的 category_id 字段存在冗余,因为商品属性存在层级关系,当通过 category_id 查找商品属性时,首先找到父属性,然后通过父属性找到子属性,此时子属性的 category_id 是没有意义的,它的值和父属性的 category_id字段相同。若要消除冗余,应将父属性和子属性拆分成两张表,但这里为了简化操作,没有进行拆分,在实际开发中应避免这个冗余字段出现问题。
        接下来通过表 4-19~表 4-22 展示上述4张表的表结构,

下面根据表 4-19~表 4-22 所示的表结构,创建上述 4张表对应的 SQL, 语句如下

#商品属性表
CREATE TABLE sh goods attr(
id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT '属性 id',
parent id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级屈件 id'

Category id INT UNSIGNED NOT NULL DEFAULT O COMMENT '商品分类 id',
name VARCHAR(80)NOT NULL DEFAULT''COMMENT'名称'
SOrt INT NOT NULL DEFAULT O COMMENT '排序'
)ENGINE-InnODB DEFAULT CHARSET- utf8;
#商品属性值表
CREATE TABLE sh goods attr value (
id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT '属性值 id',goods id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品 id',attr id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '屈性 id'.attr value VARCHAR(80)NOT NULL DEFAULT ''COMMENT'属性值')ENGINE-InnODB DEFAULT CHARSET-utf8;
#商品筛选表
CREATE TABLE sh goods selector(
id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT '价选 id'parent id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上级筛选 id',category id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品分类 id',name VARCHAR(80)NOT NULL DEFAULT"COMMENT 名称'
SOrt INT NOT NULL DEFAULT 0 COMMENT '排序'
)ENGINE-InnODB DEFAULT CHARSET-utf8;
#商品筛选值表
CREATE TABLE sh goods selector value(
id INT UNSIGNED PRIMARY KEY AUTO INCREMENT COMMENT '鍗选值 id'goods id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '商品 id',selector id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '筛选 id'.selector value VARCHAR(80)NOT NULL DEFAULT ''COMMENT '筛选值)ENGINE= InnODB DEFAULT CHARSET-utf8;
完成数据表的创建后,为上述4张表添加测试数据,具体 SQL 语句如下
INSERT INTo sh goods attr VALUES
(1，0，6，'基本信息'，0)，(2，1，6，'机身颜色'，0)
(3，1，6，'输入方式'，1)，(4，1，6，'操作系统'，2)，
(5，0，6，'屏幕'，1)，(6,5,6，'屏幕尺寸'，0)，(7，5,6，'屏幕材质'，1)，
(8，5，6，"分辨率"，2)，(9，0，6，"摄像头'，2)，
(10，9，6，'前置摄像头'，0)，(11，9，6，'后置摄像头'，1)，
(12，0，6，"电池信息'，3)，(13，12，6，'电池容量'，0)，
(14，12，6，"是否可拆卸'，1);
INSERT INTo sh goods attr value VALUEs
(1，5，2，'黑色')，(2，5，3，'触摸屏')，(3，5，4，'Android'),
(4,5,6,'5.5寸'),(5,5,7,"IPS'),(6,5,8,'1920*1080')，
(7,5，10，'1600万')，(8，5，11,'800万'),
(9，5，13，"3500mah')，(10，5，14，否');

        小提示:若希望筛选范围是一个区间(如手机的存储容量有1GB、2GB、4GB等选择)，有两种实现思路,一种是使用描述范围(如“2GB 以上”“1GB~2GB”)作为筛选项,另一种是在 sh goods_selector_value 表中增加一个数字类型的字段,专门保存数字类型的属性值,在查询时使用大于、小于运算符来表示一个区间。