数据库设计报告

已于 2025-03-01 20:02:31 修改
阅读量962 收藏 29
点赞数 8
分类专栏： SpringBoot+Vue实现外卖小程序全过程 文章标签： 数据库
于 2025-03-01 19:34:06 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/aystl_gss/article/details/145951872
版权

一、概述

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

二、逻辑模型


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', '小明', 'https://example.com/avatar1.jpg'),
('openid_654321', '13800138001', '小红', 'https://example.com/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语句的详细规划，实现了「晨光宅配」小程序的核心业务需求，并通过索引、分库分表和缓存策略优化了性能。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

爱因斯坦乐

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值