上述数据表设计方案适用于商品类别差异不是很大的情形,通过表的字段可以发现不同的商品之间变化的信息只有 attribute_list 字段, 而这个字段通过json来存储各种不同的属性集合, 同样sku表中变化的字段只有 product_specs 也是通过json来存储各种不同属性组合。
将tb_product、tb_brand、tb_product_specs表中的info字段分别抽取到单独的表中。
/*
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 ‘类别编号’,
name
varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘类别名称’,
status
tinyint(0) UNSIGNED NULL DEFAULT 1 COMMENT ‘状态:1上架 2下架’,
priority
int(0) UNSIGNED NULL DEFAULT NULL COMMENT ‘优先级’,
pid
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 pid
(pid
) USING BTREE,
CONSTRAINT tb_product_category_ibfk_1
FOREIGN KEY (pid
) REFERENCES tb_product_category
(id
) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘商品类别表’ ROW_FORMAT = Dynamic;
– Records of tb_product_category
INSERT INTO tb_product_category
VALUES (1, ‘aa’, 1, NULL, NULL, ‘2021-12-07 10:34:29’, ‘2021-12-07 10:34:29’);
INSERT INTO tb_product_category
VALUES (2, ‘bb’, 1, NULL, 1, ‘2021-12-07 10:34:36’, ‘2021-12-07 10:34:36’);
INSERT INTO tb_product_category
VALUES (3, ‘cc’, 1, NULL, 2, ‘2021-12-07 10:34:42’, ‘2021-12-07 10:34:42’);
INSERT INTO tb_product_category
VALUES (4, ‘dd’, 1, NULL, 3, ‘2021-12-07 10:34:51’, ‘2021-12-07 10:34:51’);
– Table structure for tb_product_specs
DROP TABLE IF EXISTS tb_product_specs
;
CREATE TABLE tb_product_specs
(
自我介绍一下,小编13年上海交大毕业,曾经在小公司待过,也去过华为、OPPO等大厂,18年进入阿里一直到现在。
深知大多数前端工程师,想要提升技能,往往是自己摸索成长或者是报班学习,但对于培训机构动则几千的学费,着实压力不小。自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
因此收集整理了一份《2024年Web前端开发全套学习资料》,初衷也很简单,就是希望能够帮助到想自学提升又不知道该从何学起的朋友,同时减轻大家的负担。
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,基本涵盖了95%以上前端开发知识点,真正体系化!
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
如果你觉得这些内容对你有帮助,可以扫码获取!!(备注:前端)
最后
前端校招精编面试解析大全点击这里获取完整版pdf查看
正体系化!**
[外链图片转存中…(img-6TeAcSsN-1712562855931)]
由于文件比较大,这里只是将部分目录截图出来,每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频,并且会持续更新!
如果你觉得这些内容对你有帮助,可以扫码获取!!(备注:前端)
最后
前端校招精编面试解析大全点击这里获取完整版pdf查看