go-catering项目-功能数据表分析设计-商品模块

商品模块

参考瑞幸app的功能模块

入口（更新中）

菜单列表

界面展示：侧边栏，商品列表

业务流程：

1. 点击侧边栏，定位到某模块的商品列表。
2. 点击商品，进入到商品详情页
3. 某模块的商品列表含有商品分类的标签，可供再次定位到具体分类的位置

商品详情

界面展示：商品对应的banner位，商品的详情信息

业务流程：

1. 商品原料若有多个，则可进行选择（单选）。

2. 商品属性有多个，也可进行选择，例如甜度：全糖、半糖、无糖等等

3. 商品详情目前是采用多个图来进行阐述。

功能拆分

1. banner位将为会拆分到banner模块来进行详细的定义和具体的位置绑定

数据表

商品表

用来展示商品的具体信息

---

-- Table structure for product

---

DROP TABLE IF EXISTS product;
CREATE TABLE product (
id int NOT NULL AUTO_INCREMENT,
category_id int NULL DEFAULT NULL COMMENT '分类id',
product_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商品名称',
description varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商品描述',
specis int NULL DEFAULT NULL COMMENT '商品规格，1->单品，2->套餐',
url varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'icon地址',
original_price int NULL DEFAULT NULL COMMENT '原价',
pay_price int NULL DEFAULT NULL COMMENT '支付价格',
discount int NULL DEFAULT NULL COMMENT '促销折扣',
status tinyint NULL DEFAULT NULL COMMENT '状态',
sort tinyint NULL DEFAULT NULL COMMENT '排序',
created_time timestamp(0) NULL DEFAULT NULL,
updated_time timestamp(0) NULL DEFAULT NULL,
deleted_time timestamp(0) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX category_id(category_id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品表' ROW_FORMAT = DYNAMIC;

商品属性表

用来展示商品的属性，例如冰量，甜度等

---

-- Table structure for product_attribute

---

DROP TABLE IF EXISTS product_attribute;
CREATE TABLE product_attribute (
id int NOT NULL AUTO_INCREMENT,
attribute_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '属性名称',
status int NOT NULL COMMENT '状态',
created_time timestamp(0) NOT NULL,
updated_time timestamp(0) NOT NULL,
deleted_time timestamp(0) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品属性表' ROW_FORMAT = DYNAMIC;

商品属性值表

用于显示商品属性的对应值，例如属性为甜度，则属性值为全糖、半糖、无糖

---

-- Table structure for product_attribute_value

---

DROP TABLE IF EXISTS product_attribute_value;
CREATE TABLE product_attribute_value (
id int NOT NULL AUTO_INCREMENT,
attribute_id int NOT NULL COMMENT '绑定的属性id',
attribute_value varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '属性值',
status int NOT NULL COMMENT '状态',
created_time timestamp(0) NOT NULL,
updated_time timestamp(0) NOT NULL,
deleted_time timestamp(0) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX attribute_index(attribute_id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品属性值表' ROW_FORMAT = DYNAMIC;

商品和商品属性的多对多关联表

---

-- Table structure for product_attribute_relation

---

DROP TABLE IF EXISTS product_attribute_relation;
CREATE TABLE product_attribute_relation (
id int NOT NULL AUTO_INCREMENT,
product_id int NOT NULL,
attribute_id int NOT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX product_attribute_index(product_id, attribute_id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品和商品属性的多对多关联表' ROW_FORMAT = DYNAMIC;

商品原料表

用于展示商品原料信息

---

-- Table structure for product_batch

---

DROP TABLE IF EXISTS product_batch;
CREATE TABLE product_batch (
id int NOT NULL AUTO_INCREMENT,
batch_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '原料名称',
status tinyint NOT NULL COMMENT '状态',
sort tinyint NOT NULL COMMENT '排序',
created_time timestamp(0) NOT NULL,
updated_time timestamp(0) NOT NULL,
deleted_time timestamp(0) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品原料表' ROW_FORMAT = DYNAMIC;

商品和商品原料的多对多关联表

---

-- Table structure for product_batch_relation

---

DROP TABLE IF EXISTS product_batch_relation;
CREATE TABLE product_batch_relation (
id int NOT NULL AUTO_INCREMENT,
product_id int NOT NULL,
batch_id int NOT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX product_batch_index(product_id, batch_id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品和商品原料的多对多关联表' ROW_FORMAT = DYNAMIC;

商品分类表

用于展示商品分类的标签

---

-- Table structure for product_category

---

DROP TABLE IF EXISTS product_category;
CREATE TABLE product_category (
id int NOT NULL AUTO_INCREMENT,
category_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '分类名称',
status int NOT NULL COMMENT '状态',
created_time timestamp(0) NOT NULL,
updated_time timestamp(0) NOT NULL,
deleted_time timestamp(0) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = DYNAMIC;

商品集合表

用于菜单页，侧边栏的商品集合

---

-- Table structure for product_collection

---

DROP TABLE IF EXISTS product_collection;
CREATE TABLE product_collection (
id int NOT NULL AUTO_INCREMENT,
name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '集合名称',
description varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '集合描述',
sort tinyint NOT NULL COMMENT '集合排序',
status tinyint NOT NULL DEFAULT 1 COMMENT '状态',
created_time timestamp(0) NOT NULL,
updated_time timestamp(0) NOT NULL,
deleted_time timestamp(0) NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品集合表' ROW_FORMAT = Dynamic;

商品和商品集合的多对多关联表

---

-- Table structure for product_collection_relation

---

DROP TABLE IF EXISTS product_collection_relation;
CREATE TABLE product_collection_relation (
id int NOT NULL,
product_id int NOT NULL,
collection_id int NOT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX product_collection_index(collection_id, product_id) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品和商品集合的多对多关联表' ROW_FORMAT = Dynamic;

商品规格，套餐和单品的关联表

---

-- Table structure for product_relation

---

DROP TABLE IF EXISTS product_relation;
CREATE TABLE product_relation (
id int NOT NULL AUTO_INCREMENT,
parent_product_id int NOT NULL,
child_product_id int NOT NULL,
PRIMARY KEY (id) USING BTREE,
INDEX index(parent_product_id, child_product_id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品规格，套餐和单品的关联表' ROW_FORMAT = DYNAMIC;