【精品】电商项目 中 基于SPU与SKU的 商品 数据库表设计

属性：id,product_id,product_specs等

属性key表：tb_attribute_key

用于存储sku的属性,比如：机身颜色，存储容量

属性key表和属性value表仅用于管理后台页面生成属性选项，管理员在发布新商品时勾选属性，方便规格的录入和保证正确性；

属性value表：tb_attribute_value

用于存储sku的属性值,比如：6+128G，8+128G，8+256G

总结

上述数据表设计方案适用于商品类别差异不是很大的情形，通过表的字段可以发现不同的商品之间变化的信息只有 attribute_list 字段， 而这个字段通过json来存储各种不同的属性集合， 同样sku表中变化的字段只有 product_specs 也是通过json来存储各种不同属性组合。

改进方案

---

将tb_product、tb_brand、tb_product_specs表中的info字段分别抽取到单独的表中。

附：相关表的SQL文件

---

/*

Navicat Premium Data Transfer

Source Server : localhost

Source Server Type : MySQL

Source Server Version : 80025

Source Host : localhost:3306

Source Schema : test

Target Server Type : MySQL

Target Server Version : 80025

File Encoding : 65001

Date: 07/12/2021 11:19:44

*/

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

---

– Table structure for tb_attribute_key

---

DROP TABLE IF EXISTS tb_attribute_key;

CREATE TABLE tb_attribute_key (

id int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘编号’,

attribute_name varchar(22) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘属性名’,

priority int(0) UNSIGNED NULL DEFAULT NULL COMMENT ‘优先级’,

category_id int(0) UNSIGNED NULL DEFAULT NULL COMMENT ‘类别编号’,

create_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT ‘创建时间’,

update_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT ‘更新时间’,

PRIMARY KEY (id) USING BTREE,

INDEX category_id(category_id) USING BTREE,

CONSTRAINT tb_attribute_key_ibfk_1 FOREIGN KEY (category_id) REFERENCES tb_product_category (id) ON DELETE RESTRICT ON UPDATE RESTRICT

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘属性key表’ ROW_FORMAT = Dynamic;

---

– Records of tb_attribute_key

---

---

– Table structure for tb_attribute_value

---

DROP TABLE IF EXISTS tb_attribute_value;

CREATE TABLE tb_attribute_value (

id int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘编号’,

attribute_id int(0) UNSIGNED NULL DEFAULT NULL COMMENT ‘属性编号’,

priority int(0) UNSIGNED NULL DEFAULT NULL COMMENT ‘优先级’,

attribute_value varchar(22) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘属性值’,

create_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT ‘创建时间’,

update_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT ‘更新时间’,

PRIMARY KEY (id) USING BTREE,

INDEX category_id(attribute_id) USING BTREE,

CONSTRAINT tb_attribute_value_ibfk_1 FOREIGN KEY (attribute_id) REFERENCES tb_attribute_key (id) ON DELETE RESTRICT ON UPDATE RESTRICT

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘属性value表’ ROW_FORMAT = Dynamic;

---

– Records of tb_attribute_value

---

---

– Table structure for tb_brand

---

DROP TABLE IF EXISTS tb_brand;

CREATE TABLE tb_brand (

id int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘品牌编号’,

name varchar(18) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘品牌名称’,

logo varchar(180) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘品牌LOGO’,

info varchar(220) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘品牌描述’,

status tinyint(0) UNSIGNED NULL DEFAULT 1 COMMENT ‘状态 1上架 2下架’,

create_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT ‘创建时间’,

update_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT ‘更新时间’,

PRIMARY KEY (id) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘品牌表’ ROW_FORMAT = Dynamic;

---

– Records of tb_brand

---

---

– Table structure for tb_product

---

DROP TABLE IF EXISTS tb_product;

CREATE TABLE tb_product (

id int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘商品编号’,

name varchar(28) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘商品名称’,

attribute_list json NULL COMMENT ‘商品属性列表’,

category_id int(0) UNSIGNED NULL DEFAULT NULL COMMENT ‘类别编号’,

brand_id int(0) UNSIGNED NULL DEFAULT NULL COMMENT ‘品牌编号’,

status tinyint(0) UNSIGNED NULL DEFAULT NULL COMMENT ‘状态 1上架 2下架’,

info varchar(180) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘备注’,

create_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT ’ 创建时间’,

update_time datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT ’ 更新时间’,

PRIMARY KEY (id) USING BTREE,

INDEX category_id(category_id) USING BTREE,

INDEX brand_id(brand_id) USING BTREE,

CONSTRAINT tb_product_ibfk_1 FOREIGN KEY (category_id) REFERENCES tb_product_category (id) ON DELETE RESTRICT ON UPDATE RESTRICT,

CONSTRAINT tb_product_ibfk_2 FOREIGN KEY (brand_id) REFERENCES tb_brand (id) ON DELETE RESTRICT ON UPDATE RESTRICT

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘商品表（SPU）’ ROW_FORMAT = Dynamic;

---

– Records of tb_product

---

INSERT INTO tb_product VALUES (1, ‘IPHONE8’, ‘{“商品简介”: {“品牌”: “Apple”, “系统”: “iOS”, “充电器”: “其他”, “分辨率”: “其他”, “支持IPv6”: “支持IPv6”, “商品产地”: “中国大陆”, “商品名称”: “AppleiPhone 13”, “商品毛重”: “320.00g”, “商品编号”: “100014352501”, “机身存储”: “256GB”, “运行内存”: “其他”}, “规格与包装”: {“主体”: {“品牌”: “Apple”, “上市年份”: “2021年”, “上市月份”: “9月”, “产品名称”: “Apple iPhone 13 (A2634) 256GB 午夜色 支持移动联通电信5G 双卡双待手机”, “入网型号”: “A2634”, “首销日期”: “24日”}, “基本信息”: {“机身材质”: “以官网信息为准”, “机身重量（g）”: “173g”, “机身厚度（mm）”: “7.65mm”, “机身宽度（mm）”: “71.5mm”, “机身长度（mm）”: “146.7mm”}}}’, NULL, NULL, NULL, NULL, ‘2021-12-07 10:52:15’, ‘2021-12-07 10:52:15’);

---

– Table structure for tb_product_category

---

DROP TABLE IF EXISTS tb_product_category;

CREATE TABLE tb_product_category (

id int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘类别编号’,

1200页Java架构面试专题及答案

小编整理不易，对这份1200页Java架构面试专题及答案感兴趣劳烦帮忙转发/点赞

百度、字节、美团等大厂常见面试题

NSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘类别编号’,

1200页Java架构面试专题及答案

小编整理不易，对这份1200页Java架构面试专题及答案感兴趣劳烦帮忙转发/点赞

[外链图片转存中…(img-X5EkBPO8-1714533780166)]

[外链图片转存中…(img-ZNKV243I-1714533780167)]

百度、字节、美团等大厂常见面试题

[外链图片转存中…(img-WupWLMZP-1714533780167)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录