业务设计-商品表sku通用设计

本文详细介绍了如何在设计商品详情页的SKU时,提供了一个ER图和DDL示例,包括`t_attribute`、`t_category`、`t_sku`、`t_sku_attribute`和`t_spu`等表的结构,以及如何关联商品分类和属性信息。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

业务设计-商品表sku通用设计

在设计商品详情页的sku时,可以参考如下设计

在这里插入图片描述
ER图:
在这里插入图片描述
具体的DDL:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_attribute
-- ----------------------------
DROP TABLE IF EXISTS `t_attribute`;
CREATE TABLE `t_attribute`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `attribute_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '属性名称',
  `type` tinyint(1) NULL DEFAULT NULL COMMENT '分类: 0 基础,1销售',
  `catetory_id` bigint NOT NULL COMMENT '分类id',
  `created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品所有属性定义' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_category
-- ----------------------------
DROP TABLE IF EXISTS `t_category`;
CREATE TABLE `t_category`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `parent_id` bigint NULL DEFAULT NULL COMMENT '父id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '分类名称',
  `created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品分类' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_sku
-- ----------------------------
DROP TABLE IF EXISTS `t_sku`;
CREATE TABLE `t_sku`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `spu_id` int NULL DEFAULT NULL COMMENT 'spu_id',
  `price` bigint NULL DEFAULT NULL COMMENT '价格,分',
  `created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `spu_id`(`spu_id` ASC) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '库存价格' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_sku_attribute
-- ----------------------------
DROP TABLE IF EXISTS `t_sku_attribute`;
CREATE TABLE `t_sku_attribute`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `sku_id` bigint NULL DEFAULT NULL COMMENT 'sku_id',
  `attribute_id` bigint NULL DEFAULT NULL COMMENT '属性id',
  `attribute_value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '属性值',
  `created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '库存属性值' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_spu
-- ----------------------------
DROP TABLE IF EXISTS `t_spu`;
CREATE TABLE `t_spu`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `spu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商品名称',
  `catetory_id` bigint NULL DEFAULT NULL COMMENT '分类id',
  `status` int NULL DEFAULT NULL COMMENT '状态: 0、下架,1、上架',
  `created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_spu_attribute
-- ----------------------------
DROP TABLE IF EXISTS `t_spu_attribute`;
CREATE TABLE `t_spu_attribute`  (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
  `spu_id` int NULL DEFAULT NULL COMMENT 'spu_id',
  `attribute_id` bigint NULL DEFAULT NULL COMMENT '属性id',
  `created_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品基本属性表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

模拟数据:

INSERT INTO `t_attribute` (`id`, `attribute_name`, `type`, `catetory_id`, `created_time`) VALUES (1, '品牌', 0, 1, '2024-01-28 15:02:28');
INSERT INTO `t_attribute` (`id`, `attribute_name`, `type`, `catetory_id`, `created_time`) VALUES (2, '机身内存', 0, 1, '2024-01-28 15:02:31');
INSERT INTO `t_attribute` (`id`, `attribute_name`, `type`, `catetory_id`, `created_time`) VALUES (3, '运行内存', 0, 1, '2024-01-28 15:03:01');
INSERT INTO `t_attribute` (`id`, `attribute_name`, `type`, `catetory_id`, `created_time`) VALUES (4, '型号', 1, 1, '2024-01-28 15:03:44');
INSERT INTO `t_attribute` (`id`, `attribute_name`, `type`, `catetory_id`, `created_time`) VALUES (5, '颜色', 1, 1, '2024-01-28 15:03:46');
INSERT INTO `t_category` (`id`, `parent_id`, `name`, `created_time`) VALUES (1, 0, '手机', '2024-01-28 14:58:03');
INSERT INTO `t_category` (`id`, `parent_id`, `name`, `created_time`) VALUES (2, 0, '电脑', '2024-01-28 14:58:07');
INSERT INTO `t_sku` (`id`, `spu_id`, `price`, `created_time`) VALUES (1, 1, 699900, '2024-01-28 15:07:40');
INSERT INTO `t_sku` (`id`, `spu_id`, `price`, `created_time`) VALUES (2, 1, 799900, '2024-01-28 15:07:40');
INSERT INTO `t_sku` (`id`, `spu_id`, `price`, `created_time`) VALUES (3, 2, 299900, '2024-01-28 15:07:40');
INSERT INTO `t_sku_attribute` (`id`, `sku_id`, `attribute_id`, `attribute_value`, `created_time`) VALUES (1, 1, 4, '12G+512G', '2024-01-28 15:09:31');
INSERT INTO `t_sku_attribute` (`id`, `sku_id`, `attribute_id`, `attribute_value`, `created_time`) VALUES (2, 1, 5, '雅丹黑', '2024-01-28 15:09:31');
INSERT INTO `t_sku_attribute` (`id`, `sku_id`, `attribute_id`, `attribute_value`, `created_time`) VALUES (3, 2, 4, '8G+512G', '2024-01-28 15:09:31');
INSERT INTO `t_sku_attribute` (`id`, `sku_id`, `attribute_id`, `attribute_value`, `created_time`) VALUES (4, 2, 5, '雅丹黑', '2024-01-28 15:09:31');
INSERT INTO `t_sku_attribute` (`id`, `sku_id`, `attribute_id`, `attribute_value`, `created_time`) VALUES (5, 3, 4, '8G+512G', '2024-01-28 15:09:31');
INSERT INTO `t_sku_attribute` (`id`, `sku_id`, `attribute_id`, `attribute_value`, `created_time`) VALUES (6, 3, 5, '雅丹黑', '2024-01-28 15:09:31');
INSERT INTO `t_spu` (`id`, `spu_name`, `catetory_id`, `status`, `created_time`) VALUES (1, '华为/HUAWEI Mate 60', 1, 1, '2024-01-28 15:04:46');
INSERT INTO `t_spu` (`id`, `spu_name`, `catetory_id`, `status`, `created_time`) VALUES (2, '华为/HUAWEI Mate 60 Pro', 1, 1, '2024-01-28 15:05:04');
INSERT INTO `t_spu_attribute` (`id`, `spu_id`, `attribute_id`, `created_time`) VALUES (1, 1, 1, '2024-01-28 15:05:23');
INSERT INTO `t_spu_attribute` (`id`, `spu_id`, `attribute_id`, `created_time`) VALUES (2, 1, 2, '2024-01-28 15:05:23');
INSERT INTO `t_spu_attribute` (`id`, `spu_id`, `attribute_id`, `created_time`) VALUES (3, 1, 3, '2024-01-28 15:05:23');
INSERT INTO `t_spu_attribute` (`id`, `spu_id`, `attribute_id`, `created_time`) VALUES (4, 2, 1, '2024-01-28 15:05:23');
INSERT INTO `t_spu_attribute` (`id`, `spu_id`, `attribute_id`, `created_time`) VALUES (5, 2, 2, '2024-01-28 15:05:23');
INSERT INTO `t_spu_attribute` (`id`, `spu_id`, `attribute_id`, `created_time`) VALUES (6, 2, 3, '2024-01-28 15:05:23');

查询 详情信息

SELECT
 ts.id,
	tc.`name`,
	tss.spu_name,
	ts.price,
	GROUP_CONCAT(tsa.attribute_value)
FROM
	t_sku ts
	LEFT JOIN t_sku_attribute tsa ON ts.id = tsa.sku_id
	LEFT JOIN t_attribute ta ON ta.id = tsa.attribute_id
	LEFT JOIN t_category tc ON tc.id = ta.catetory_id
	LEFT JOIN t_spu tss ON tss.id = ts.spu_id 
	AND tss.`status` =1
	WHERE 1=1
	GROUP BY ts.id,tc.`name`,tss.spu_name

结果

在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值