225 lines
7.7 KiB
Markdown
225 lines
7.7 KiB
Markdown
我来设计一个具有复杂关联关系和迷惑项的电商数据库模型：
|
||
|
||
## 数据库：E-Commerce System
|
||
|
||
### 表设计（包含迷惑项）
|
||
|
||
#### 1. 用户相关表
|
||
```sql
|
||
-- 用户主表（与客户表名称相似，容易混淆）
|
||
CREATE TABLE users (
|
||
user_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
username VARCHAR(50) UNIQUE NOT NULL,
|
||
email VARCHAR(100) UNIQUE NOT NULL,
|
||
phone_number VARCHAR(20),
|
||
created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
status ENUM('active', 'inactive', 'suspended') DEFAULT 'active'
|
||
);
|
||
|
||
-- 客户信息表（与用户表相似，但有不同用途）
|
||
CREATE TABLE customers (
|
||
customer_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
user_id INT NOT NULL, -- 外键，但名称与users表主键不完全匹配
|
||
full_name VARCHAR(100),
|
||
birth_date DATE,
|
||
registration_date DATE,
|
||
customer_type ENUM('individual', 'business', 'vip') DEFAULT 'individual',
|
||
FOREIGN KEY (user_id) REFERENCES users(id) -- 故意写错：应该是user_id REFERENCES users(user_id)
|
||
);
|
||
|
||
-- 用户地址表
|
||
CREATE TABLE user_addresses (
|
||
address_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
user_id INT NOT NULL,
|
||
customer_id INT, -- 冗余字段，增加复杂度
|
||
address_line1 VARCHAR(200),
|
||
address_line2 VARCHAR(200),
|
||
city_id INT,
|
||
postal_code VARCHAR(20),
|
||
is_default BOOLEAN DEFAULT FALSE,
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(user_id),
|
||
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
|
||
);
|
||
```
|
||
|
||
#### 2. 商品相关表
|
||
```sql
|
||
-- 商品表
|
||
CREATE TABLE products (
|
||
product_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
product_code VARCHAR(50) UNIQUE NOT NULL, -- 与sku相似
|
||
product_name VARCHAR(200) NOT NULL,
|
||
description TEXT,
|
||
price DECIMAL(10,2) NOT NULL,
|
||
cost_price DECIMAL(10,2), -- 与price相似
|
||
category_id INT,
|
||
supplier_id INT,
|
||
stock_quantity INT DEFAULT 0,
|
||
reorder_level INT DEFAULT 10,
|
||
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
modified_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- 产品表（名称与products相似，但内容不同）
|
||
CREATE TABLE items (
|
||
item_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
product_id INT NOT NULL,
|
||
sku VARCHAR(50) UNIQUE, -- 与product_code相似
|
||
variant_name VARCHAR(100),
|
||
additional_price DECIMAL(10,2) DEFAULT 0,
|
||
inventory_count INT DEFAULT 0,
|
||
FOREIGN KEY (product_id) REFERENCES products(product_id)
|
||
);
|
||
|
||
-- 商品分类表
|
||
CREATE TABLE categories (
|
||
category_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
category_name VARCHAR(100) NOT NULL,
|
||
parent_category_id INT, -- 自引用，实现层级分类
|
||
category_path VARCHAR(500),
|
||
FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
|
||
);
|
||
```
|
||
|
||
#### 3. 订单相关表
|
||
```sql
|
||
-- 订单主表
|
||
CREATE TABLE orders (
|
||
order_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
order_number VARCHAR(50) UNIQUE NOT NULL,
|
||
customer_id INT NOT NULL, -- 注意：这里引用customers表，不是users表
|
||
user_id INT, -- 冗余字段
|
||
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
order_status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
|
||
total_amount DECIMAL(10,2),
|
||
net_amount DECIMAL(10,2), -- 与total_amount相似
|
||
shipping_address_id INT,
|
||
billing_address_id INT,
|
||
payment_method VARCHAR(50)
|
||
);
|
||
|
||
-- 订单项表
|
||
CREATE TABLE order_items (
|
||
order_item_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
order_id INT NOT NULL,
|
||
item_id INT, -- 引用items表
|
||
product_id INT, -- 也引用products表，增加复杂度
|
||
quantity INT NOT NULL,
|
||
unit_price DECIMAL(10,2) NOT NULL,
|
||
discount DECIMAL(10,2) DEFAULT 0,
|
||
subtotal DECIMAL(10,2),
|
||
FOREIGN KEY (order_id) REFERENCES orders(order_id),
|
||
FOREIGN KEY (item_id) REFERENCES items(item_id),
|
||
FOREIGN KEY (product_id) REFERENCES products(product_id)
|
||
);
|
||
|
||
-- 发货表
|
||
CREATE TABLE shipments (
|
||
shipment_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
order_id INT NOT NULL,
|
||
shipment_date DATE,
|
||
estimated_delivery DATE, -- 与delivery_date相似
|
||
actual_delivery DATE, -- 与estimated_delivery相似
|
||
carrier VARCHAR(100),
|
||
tracking_number VARCHAR(100),
|
||
status ENUM('preparing', 'shipped', 'in_transit', 'delivered') DEFAULT 'preparing'
|
||
);
|
||
```
|
||
|
||
#### 4. 多对多关系表
|
||
```sql
|
||
-- 商品-供应商关系（多对多）
|
||
CREATE TABLE product_suppliers (
|
||
product_id INT NOT NULL,
|
||
supplier_id INT NOT NULL,
|
||
is_primary BOOLEAN DEFAULT FALSE,
|
||
purchase_price DECIMAL(10,2),
|
||
PRIMARY KEY (product_id, supplier_id),
|
||
FOREIGN KEY (product_id) REFERENCES products(product_id)
|
||
-- 故意不创建supplier_id的外键约束，增加测试难度
|
||
);
|
||
|
||
-- 用户-收藏夹关系（多对多）
|
||
CREATE TABLE user_wishlists (
|
||
user_id INT NOT NULL,
|
||
product_id INT NOT NULL,
|
||
added_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
priority INT DEFAULT 1,
|
||
PRIMARY KEY (user_id, product_id),
|
||
FOREIGN KEY (user_id) REFERENCES users(user_id),
|
||
FOREIGN KEY (product_id) REFERENCES products(product_id)
|
||
);
|
||
```
|
||
|
||
#### 5. 其他相关表（增加迷惑性）
|
||
```sql
|
||
-- 支付表
|
||
CREATE TABLE payments (
|
||
payment_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
order_id INT NOT NULL,
|
||
payment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
amount DECIMAL(10,2),
|
||
payment_type VARCHAR(50), -- 与payment_method相似
|
||
transaction_id VARCHAR(100),
|
||
payment_status ENUM('pending', 'completed', 'failed', 'refunded')
|
||
);
|
||
|
||
-- 评价表
|
||
CREATE TABLE reviews (
|
||
review_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
product_id INT NOT NULL,
|
||
user_id INT NOT NULL,
|
||
rating INT CHECK (rating >= 1 AND rating <= 5),
|
||
comment TEXT,
|
||
review_date DATE, -- 与created_at相似但不同
|
||
helpful_count INT DEFAULT 0
|
||
);
|
||
|
||
-- 促销表
|
||
CREATE TABLE promotions (
|
||
promo_id INT PRIMARY KEY AUTO_INCREMENT,
|
||
promo_code VARCHAR(50) UNIQUE,
|
||
promo_name VARCHAR(100),
|
||
discount_type ENUM('percentage', 'fixed', 'buy_one_get_one'),
|
||
discount_value DECIMAL(10,2),
|
||
start_date DATE,
|
||
end_date DATE,
|
||
minimum_purchase DECIMAL(10,2)
|
||
);
|
||
```
|
||
|
||
### 迷惑项总结：
|
||
1. **相似表名**：products/items, users/customers
|
||
2. **相似字段名**：price/cost_price, total_amount/net_amount, estimated_delivery/actual_delivery
|
||
3. **含义相近字段**：created_date/created_at/created_time, payment_type/payment_method
|
||
4. **冗余字段**：order_items表中同时有item_id和product_id
|
||
5. **不一致的命名**：user_id vs customer_id在不同表的引用
|
||
6. **缺失的外键**：product_suppliers表中的supplier_id没有外键约束
|
||
7. **相似的用途不同名**：product_code vs sku
|
||
8. **同义词字段**：stock_quantity vs inventory_count
|
||
|
||
### 测试查询示例：
|
||
```sql
|
||
-- 复杂查询1：找出每个用户最常购买的商品类别
|
||
-- 需要连接：users → customers → orders → order_items → products → categories
|
||
|
||
-- 复杂查询2：计算每个供应商的商品在最近一个月的销售额
|
||
-- 需要连接：product_suppliers → products → order_items → orders
|
||
|
||
-- 复杂查询3：找出有相同商品的用户对（社交推荐）
|
||
-- 需要多对多关系分析：users → user_wishlists → products ← user_wishlists ← users
|
||
|
||
-- 复杂查询4：层级分类的商品统计（递归查询）
|
||
-- 需要使用WITH RECURSIVE处理categories表的层级关系
|
||
```
|
||
|
||
这个设计包含了：
|
||
- 一对多关系（用户-地址、订单-订单项）
|
||
- 多对多关系（用户-收藏夹、商品-供应商）
|
||
- 自引用关系（分类层级）
|
||
- 复杂的业务逻辑和数据冗余
|
||
- 各种迷惑项和陷阱
|
||
|
||
你可以用这个数据库模型来测试LLM对复杂SQL查询的理解和生成能力。 |