1.设计品类表
品类编号
用来区分品类所属哪个范围,比如1000-1999属于医疗范围
CREATE TABLE t_spec_group(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
spg_id INT UNSIGNED NOT NULL COMMENT "品类编号",
`name` VARCHAR(200) NOT NULL COMMENT "品类编号",
UNIQUE INDEX unq_spg_id(spg_id),
UNIQUE INDEX unq_name(`name`),
INDEX idx_spg_id(spg_id)
) COMMENT "品类表"
UNIQUE INDEX
唯一索引
主键与唯一性索引区别
- 主键不允许空值,唯一索引允许空值
- 主键只允许一个,唯一索引允许多个
- 主键产生唯一的聚集索引,唯一索引产生唯一的非聚集索引
例子
2.设计参数表
CREATE TABLE t_spec_param(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
spg_id INT UNSIGNED NOT NULL COMMENT "品类编号",
spp_id INT UNSIGNED NOT NULL COMMENT "参数编号",
`name` VARCHAR(200) NOT NULL COMMENT "参数名称",
`numeric` BOOLEAN NOT NULL COMMENT "是否为数字参数",
unit VARCHAR(200) COMMENT "单位(量词)",
generic BOOLEAN NOT NULL COMMENT "是否为通用参数",
searching BOOLEAN NOT NULL COMMENT "是否用于通用搜索",
segements VARCHAR(200) COMMENT "参数值",
INDEX idx_spg_id(spg_id),
INDEX idx_spp_id(spp_id),
) COMMENT "参数表"
2.设计品牌表
CREATE TABLE t_brand(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
`name` VARCHAR(200) NOT NULL COMMENT "品牌名称",
image VARCHAR(200) COMMENT "图片网址",
letter CHAR(1) NOT NULL COMMENT "品牌首字母",
UNIQUE INDEX unq_name(`name`),
INDEX idx_letter(letter)
) COMMENT "品牌表"
例子
3.商品分类表
CREATE TABLE t_category(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
`name` VARCHAR(200) NOT NULL COMMENT "分类名称",
parent_id INT UNSIGNED COMMENT "上级分类ID",
if_parent BOOLEAN NOT NULL COMMENT "是否含有下级分类",
sort INT UNSIGNED NOT NULL COMMENT "排名指数",
INDEX idx_parent_id(parent_id),
INDEX idx_sort(sort)
) COMMENT "商品分类表"
例子
4.设计分类与品牌关联表
CREATE TABLE t_category_brand(
category_id INT UNSIGNED COMMENT "分类ID",
brand_id INT UNSIGNED COMMENT "品牌ID",
PRIMARY KEY(category_id, brand_id)
) COMMENT "分类与品牌关联表"
5.设计产品表
CREATE TABLE t_spu(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
title VARCHAR(200) NOT NULL COMMENT "标题",
sub_title VARCHAR(200) NOT NULL COMMENT "副标题",
category_id INT UNSIGNED NOT NULL COMMENT "分类ID",
brand_id INT UNSIGNED NOT NULL COMMENT "品牌ID",
spg_id INT UNSIGNED NOT NULL COMMENT "品类ID",
saleable BOOLEAN NOT NULL COMMENT "是否上架",
vaild BOOLEAN NOT NULL COMMENT "是否有效",
create_time TIMESTAMP NOT NULL DEFAULT NOW() COMMENT "添加时间",
last_update_time TIMESTAMP NOT NULL DEFAULT NOW() COMMENT "最后修改时间",
INDEX idx_brand_id(brand_id),
INDEX idx_category_id(category_id),
INDEX idx_spg_id(spg_id),
INDEX idx_saleable(saleable),
INDEX idx_saleable(vaild)
) COMMENT "产品表"
6.设计商品表
CREATE TABLE t_sku(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
spu_id INT UNSIGNED NOT NULL COMMENT "产品ID",
title VARCHAR(200) NOT NULL COMMENT "商品标题",
images JSON COMMENT "商品图片",
price DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT "价格",
param JSON NOT NULL COMMENT "参数",
saleable BOOLEAN NOT NULL COMMENT "是否上架",
vaild BOOLEAN NOT NULL COMMENT "是否有效",
create_time TIMESTAMP NOT NULL DEFAULT NOW() COMMENT "添加时间",
last_update_time TIMESTAMP NOT NULL DEFAULT NOW() COMMENT "最后修改时间",
INDEX idx_spu_id(spu_id),
INDEX idx_saleable(saleable),
INDEX idx_saleable(vaild)
) COMMENT "商品表"
例子
param
里的值是根据参数表设置的