数据库设计报告

一、概述

本数据库设计用于支持「晨光宅配」小程序的业务需求，涵盖用户、商家、配送员、订单、评价等核心模块。设计目标是保证数据一致性、查询性能和高可用性。

二、逻辑模型 1. 实体关系图（ERD）

```mermaid erDiagram     user ||--o{ address : "1:N"     user ||--o{ subscription : "1:N"     user ||--o{ order : "1:N"     user ||--o{ review : "1:N"     merchant ||--o{ meal : "1:N"     merchant ||--o{ delivery_area : "1:N"     meal ||--o{ subscription : "1:N"     meal ||--o{ order_item : "1:N"     orders ||--o{ order_item : "1:N"     orders ||--o{ delivery : "1:1"     orders ||--o{ review : "1:1"     delivery ||--o{ rider : "N:1"     rider ||--o{ rider_area : "N:M"     delivery_area ||--o{ rider_area : "1:M" ```

2. 实体说明 实体名说明user用户信息address用户地址merchant商家信息meal套餐信息subscription用户订阅信息orders订单信息order_item订单项信息（一个订单可包含多个套餐）rider配送员信息delivery配送信息delivery_area配送区域信息

review

用户评价信息rider_area配送员区域信息 三、物理模型 表结构设计 **用户表（user）**

| 字段名         | 类型         | 说明                     | 约束               | |----------------|--------------|--------------------------|--------------------| | user_id        | BIGINT       | 用户ID（主键）           | 自增               | | openid         | VARCHAR(64)  | 微信OpenID               | 唯一索引           | | phone          | VARCHAR(11)  | 手机号                   | 唯一索引           | | nickname       | VARCHAR(32)  | 昵称                     |                    | | avatar_url     | VARCHAR(128) | 头像URL                  |                    | | create_time    | DATETIME     | 注册时间                 | 默认当前时间       |

**地址表（address）**

| 字段名         | 类型         | 说明                     | 约束               | |----------------|--------------|--------------------------|--------------------| | address_id     | BIGINT       | 地址ID（主键）           | 自增               | | user_id        | BIGINT       | 用户ID                   | 外键（user.user_id）| | contact_name   | VARCHAR(32)  | 联系人姓名               |                    | | contact_phone  | VARCHAR(11)  | 联系人手机号             |                    | | detail_address | VARCHAR(128) | 详细地址                 |                    | | is_default     | TINYINT      | 是否默认地址（0否/1是）  | 默认0              |

**商家表（merchant）**

| 字段名         | 类型         | 说明                     | 约束               | |----------------|--------------|--------------------------|--------------------| | merchant_id    | BIGINT       | 商家ID（主键）           | 自增               | | name           | VARCHAR(64)  | 商家名称                 |                    | | address        | VARCHAR(128) | 商家地址                 |                    | | geo_point      | POINT        | 地理位置（经纬度）       | 空间索引           | | max_capacity   | INT          | 每日最大产能             |                    |

**套餐表（meal）**

| 字段名         | 类型         | 说明                     | 约束               | |----------------|--------------|--------------------------|--------------------| | meal_id        | BIGINT       | 套餐ID（主键）           | 自增               | | merchant_id    | BIGINT       | 商家ID                   | 外键（merchant.merchant_id）| | name           | VARCHAR(64)  | 套餐名称                 |                    | | description    | VARCHAR(128) | 套餐描述                 |                    | | price          | DECIMAL(8,2) | 价格                     |                    | | stock          | INT          | 库存                     |                    | | is_active      | TINYINT      | 是否上架（0否/1是）      | 默认1              |

**订阅表（subscription）**

| 字段名         | 类型         | 说明                     | 约束               | |----------------|--------------|--------------------------|--------------------| | sub_id         | BIGINT       | 订阅ID（主键）           | 自增               | | user_id        | BIGINT       | 用户ID                   | 外键（user.user_id）| | meal_id        | BIGINT       | 套餐ID                   | 外键（meal.meal_id）| | cycle_type     | ENUM('daily','weekday','custom') | 周期类型 |                    | | delivery_time  | TIME         | 配送时间                 |                    | | start_date     | DATE         | 开始日期                 |                    | | end_date       | DATE         | 结束日期                 |                    | | status         | TINYINT      | 状态（0停用/1生效）      | 默认1              |

 **订单表（orders）**

| 字段名         | 类型         | 说明                     | 约束               | |----------------|--------------|--------------------------|--------------------| | order_id       | BIGINT       | 订单ID（主键）           | 自增               | | user_id        | BIGINT       | 用户ID                   | 外键（user.user_id）| | merchant_id    | BIGINT       | 商家ID                   | 外键（merchant.merchant_id）| | address_id     | BIGINT       | 地址ID                   | 外键（address.address_id）| | total_amount   | DECIMAL(10,2)| 总金额                   |                    | | status         | ENUM('pending','delivering','completed','canceled') | 订单状态 | 默认'pending'      | | create_time    | DATETIME     | 创建时间                 | 默认当前时间       |

**订单项表（order_item）**

| 字段名         | 类型         | 说明                     | 约束               | |----------------|--------------|--------------------------|--------------------| | item_id        | BIGINT       | 订单项ID（主键）         | 自增               | | order_id       | BIGINT       | 订单ID                   | 外键（order.order_id）| | meal_id        | BIGINT       | 套餐ID                   | 外键（meal.meal_id）| | quantity       | INT          | 数量                     |                    | | price          | DECIMAL(8,2) | 单价                     |                    |

 **配送员表（rider）**

| 字段名         | 类型         | 说明                     | 约束               | |----------------|--------------|--------------------------|--------------------| | rider_id       | BIGINT       | 配送员ID（主键）         | 自增               | | name           | VARCHAR(32)  | 姓名                     |                    | | phone          | VARCHAR(11)  | 手机号                   | 唯一索引           | | status         | ENUM('active','inactive') | 状态       | 默认'active'      | | geo_point      | POINT        | 实时位置（经纬度）       | 空间索引           | | last_active_time | DATETIME   | 最后活跃时间             |                    |

**配送区域表（delivery_area）**

| 字段名         | 类型         | 说明                     | 约束               | |----------------|--------------|--------------------------|--------------------| | area_id        | BIGINT       | 区域ID（主键）           | 自增               | | merchant_id    | BIGINT       | 商家ID                   | 外键（merchant.merchant_id）| | geo_polygon    | POLYGON      | 配送范围（多边形）       | 空间索引           |

**配送表（delivery）**

| 字段名         | 类型         | 说明                     | 约束               | |----------------|--------------|--------------------------|--------------------| | delivery_id    | BIGINT       | 配送ID（主键）           | 自增               | | order_id       | BIGINT       | 订单ID                   | 外键（order.order_id）| | rider_id       | BIGINT       | 配送员ID                 | 外键（rider.rider_id）| | start_time     | DATETIME     | 开始时间                 |                    | | end_time       | DATETIME     | 完成时间                 |                    | | status         | ENUM('pending','picked_up','delivered') | 配送状态 | 默认'pending'      |

**评价表（review）**

| 字段名         | 类型         | 说明                     | 约束               | |----------------|--------------|--------------------------|--------------------| | review_id      | BIGINT       | 评价ID（主键）           | 自增               | | order_id       | BIGINT       | 订单ID                   | 外键（order.order_id）| | user_id        | BIGINT       | 用户ID                   | 外键（user.user_id）| | rating         | TINYINT      | 评分（1-5星）            |                    | | content        | TEXT         | 评价内容                 |                    | | create_time    | DATETIME     | 创建时间                 | 默认当前时间       |

**配送员区域关联表（rider_area）**

| 字段名         | 类型         | 说明                     | 约束               | |----------------|--------------|--------------------------|--------------------| | rider_id       | BIGINT       | 配送员ID                 | 外键（rider.rider_id）| | area_id        | BIGINT       | 区域ID                   | 外键（delivery_area.area_id）| | PRIMARY KEY (rider_id, area_id) |  | 联合主键               |                    |

四、SQL语句 1. 建表语句 -- 创建数据库 CREATE DATABASE IF NOT EXISTS morning_box; USE morning_box; -- 用户表 CREATE TABLE IF NOT EXISTS user ( user_id BIGINT AUTO_INCREMENT PRIMARY KEY, openid VARCHAR(64) NOT NULL UNIQUE, phone VARCHAR(11) NOT NULL UNIQUE, nickname VARCHAR(32), avatar_url VARCHAR(128), create_time DATETIME DEFAULT CURRENT_TIMESTAMP ); -- 地址表 CREATE TABLE IF NOT EXISTS address ( address_id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, contact_name VARCHAR(32) NOT NULL, contact_phone VARCHAR(11) NOT NULL, detail_address VARCHAR(128) NOT NULL, is_default TINYINT DEFAULT 0, FOREIGN KEY (user_id) REFERENCES user(user_id) ); -- 商家表 CREATE TABLE IF NOT EXISTS merchant ( merchant_id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, address VARCHAR(128) NOT NULL, geo_point POINT NOT NULL, max_capacity INT NOT NULL, SPATIAL INDEX(geo_point) ); -- 套餐表 CREATE TABLE IF NOT EXISTS meal ( meal_id BIGINT AUTO_INCREMENT PRIMARY KEY, merchant_id BIGINT NOT NULL, name VARCHAR(64) NOT NULL, description VARCHAR(128), price DECIMAL(8,2) NOT NULL, stock INT NOT NULL, is_active TINYINT DEFAULT 1, FOREIGN KEY (merchant_id) REFERENCES merchant(merchant_id) ); -- 订阅表 CREATE TABLE IF NOT EXISTS subscription ( sub_id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, meal_id BIGINT NOT NULL, cycle_type ENUM('daily','weekday','custom') NOT NULL, delivery_time TIME NOT NULL, start_date DATE NOT NULL, end_date DATE, status TINYINT DEFAULT 1, FOREIGN KEY (user_id) REFERENCES user(user_id), FOREIGN KEY (meal_id) REFERENCES meal(meal_id) ); -- 订单表（改为 orders） CREATE TABLE IF NOT EXISTS orders ( order_id BIGINT AUTO_INCREMENT PRIMARY KEY, user_id BIGINT NOT NULL, merchant_id BIGINT NOT NULL, address_id BIGINT NOT NULL, total_amount DECIMAL(10,2) NOT NULL, status ENUM('pending','delivering','completed','canceled') DEFAULT 'pending', create_time DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES user(user_id), FOREIGN KEY (merchant_id) REFERENCES merchant(merchant_id), FOREIGN KEY (address_id) REFERENCES address(address_id) ); -- 订单项表 CREATE TABLE IF NOT EXISTS order_item ( item_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_id BIGINT NOT NULL, meal_id BIGINT NOT NULL, quantity INT NOT NULL, price DECIMAL(8,2) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (meal_id) REFERENCES meal(meal_id) ); -- 配送员表（先创建，确保 delivery 表可以引用） CREATE TABLE IF NOT EXISTS rider ( rider_id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(32) NOT NULL, phone VARCHAR(11) NOT NULL UNIQUE, status ENUM('active','inactive') DEFAULT 'active', geo_point POINT NOT NULL, last_active_time DATETIME, SPATIAL INDEX(geo_point) ); -- 配送表 CREATE TABLE IF NOT EXISTS delivery ( delivery_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_id BIGINT NOT NULL, rider_id BIGINT NOT NULL, start_time DATETIME, end_time DATETIME, status ENUM('pending','picked_up','delivered') DEFAULT 'pending', FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (rider_id) REFERENCES rider(rider_id) ); -- 配送区域表 CREATE TABLE IF NOT EXISTS delivery_area ( area_id BIGINT AUTO_INCREMENT PRIMARY KEY, merchant_id BIGINT NOT NULL, geo_polygon POLYGON NOT NULL, FOREIGN KEY (merchant_id) REFERENCES merchant(merchant_id), SPATIAL INDEX(geo_polygon) ); -- 评价表 CREATE TABLE IF NOT EXISTS review ( review_id BIGINT AUTO_INCREMENT PRIMARY KEY, order_id BIGINT NOT NULL UNIQUE, user_id BIGINT, rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5), content TEXT, create_time DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (user_id) REFERENCES user(user_id) ); -- 配送员区域关联表 CREATE TABLE IF NOT EXISTS rider_area ( rider_id BIGINT NOT NULL, area_id BIGINT NOT NULL, PRIMARY KEY (rider_id, area_id), FOREIGN KEY (rider_id) REFERENCES rider(rider_id), FOREIGN KEY (area_id) REFERENCES delivery_area(area_id) ); -- 示例数据插入 -- 用户表 INSERT INTO user (openid, phone, nickname, avatar_url) VALUES ('openid_123456', '13800138000', '小明', ' example /avatar1.jpg'), ('openid_654321', '13800138001', '小红', ' example /avatar2.jpg'); -- 地址表 INSERT INTO address (user_id, contact_name, contact_phone, detail_address, is_default) VALUES (1, '小明', '13800138000', '北京市朝阳区XX路XX号', 1), (2, '小红', '13800138001', '上海市浦东新区XX路XX号', 1); -- 商家表 INSERT INTO merchant (name, address, geo_point, max_capacity) VALUES ('早餐铺A', '北京市朝阳区XX路XX号', ST_GeomFromText('POINT(116.404 39.915)'), 100), ('早餐铺B', '上海市浦东新区XX路XX号', ST_GeomFromText('POINT(121.473 31.230)'), 150); -- 套餐表 INSERT INTO meal (merchant_id, name, description, price, stock) VALUES (1, '经典早餐套餐', '包含豆浆、油条、鸡蛋', 15.00, 50), (2, '轻食早餐套餐', '包含沙拉、全麦面包、果汁', 20.00, 30); -- 订阅表 INSERT INTO subscription (user_id, meal_id, cycle_type, delivery_time, start_date) VALUES (1, 1, 'daily', '07:30:00', '2023-10-01'), (2, 2, 'weekday', '08:00:00', '2023-10-01'); -- 订单表（改为 orders） INSERT INTO orders (user_id, merchant_id, address_id, total_amount, status) VALUES (1, 1, 1, 15.00, 'completed'), (2, 2, 2, 20.00, 'pending'); -- 订单项表 INSERT INTO order_item (order_id, meal_id, quantity, price) VALUES (1, 1, 1, 15.00), (2, 2, 1, 20.00); -- 配送员表 INSERT INTO rider (name, phone, geo_point) VALUES ('张三', '13800138002', ST_GeomFromText('POINT(116.404 39.915)')), ('李四', '13800138003', ST_GeomFromText('POINT(121.473 31.230)')); -- 配送区域表 INSERT INTO delivery_area (merchant_id, geo_polygon) VALUES (1, ST_GeomFromText('POLYGON((116.404 39.915, 116.404 39.920, 116.410 39.920, 116.410 39.915, 116.404 39.915))')), (2, ST_GeomFromText('POLYGON((121.473 31.230, 121.473 31.235, 121.480 31.235, 121.480 31.230, 121.473 31.230))')); -- 配送员区域关联表 INSERT INTO rider_area (rider_id, area_id) VALUES (1, 1), (2, 2); -- 评价表 INSERT INTO review (order_id, user_id, rating, content) VALUES (1, 1, 5, '配送很快，早餐很好吃！'), (2, 2, 4, '味道不错，下次还会点。');

2. 查询示例

1. 查询用户的所有订单

SELECT o.order_id, o.total_amount, o.status, m.name AS meal_name, oi.quantity FROM order o JOIN order_item oi ON o.order_id = oi.order_id JOIN meal m ON oi.meal_id = m.meal_id WHERE o.user_id = 1001;

2. 查询用户的订阅信息

SELECT s.sub_id, m.name AS meal_name, s.cycle_type, s.delivery_time FROM subscription s JOIN meal m ON s.meal_id = m.meal_id WHERE s.user_id = 1001;

3. 查询订单的配送状态

SELECT o.order_id, d.status, r.name AS rider_name FROM order o JOIN delivery d ON o.order_id = d.order_id JOIN rider r ON d.rider_id = r.rider_id WHERE o.user_id = 1001;

4.查询配送员的配送区域

SELECT a.area_id, a.geo_polygon FROM delivery_area a JOIN rider_area ra ON a.area_id = ra.area_id WHERE ra.rider_id = 2001;

5. 查询商家的热销套餐

SELECT m.meal_id, m.name, SUM(oi.quantity) AS total_sold FROM meal m JOIN order_item oi ON m.meal_id = oi.meal_id JOIN order o ON oi.order_id = o.order_id WHERE m.merchant_id = 3001 GROUP BY m.meal_id ORDER BY total_sold DESC LIMIT 5;

6. 查询用户的评价记录

SELECT r.review_id, r.rating, r.content, m.name AS meal_name FROM review r JOIN order o ON r.order_id = o.order_id JOIN order_item oi ON o.order_id = oi.order_id JOIN meal m ON oi.meal_id = m.meal_id WHERE r.user_id = 1001; 五、优化设计 1. 索引优化

   - 为高频查询字段（如`user.openid`、`order.user_id`）创建索引。      - 使用空间索引优化地理位置查询（如`merchant.geo_point`）。  

2. 分库分表

   - 按用户ID哈希分表（如`order_0`到`order_3`），分散订单数据。      - 按区域分库（如`db_shanghai`、`db_beijing`），减少跨区域查询。  

3. 缓存策略 

   - 使用Redis缓存热点数据（如用户订阅配置、商家菜单）。      - 订单状态变更时，同步更新缓存。  

六、总结

本设计通过逻辑模型、物理模型和SQL语句的详细规划，实现了「晨光宅配」小程序的核心业务需求，并通过索引、分库分表和缓存策略优化了性能。