

## 一、为什么选择 SQLite？

在讲表结构之前，先回答一个常见质疑：**"SQLite能胜任生产环境吗？"**

很多人对SQLite的印象还停留在"手机App的本地存储"，但实际上它的能力远超大多数人的认知：

| 能力指标 | SQLite 表现 |
|---------|------------|
| 单表数据量 | **TB级别**无压力（官方测试过2TB）|
| 并发读 | 完全支持（多读者不阻塞）|
| 并发写 | 串行化（同一时刻只有一个写者）|
| ACID事务 | 完整支持 |
| 备份方式 | 复制一个文件即可 |

对于学校这种**20-50人同时在线、日增几十条记录**的场景来说，SQLite不仅够用，而且是**最佳选择**——没有MySQL的连接池配置、没有主从同步、没有定时备份脚本（复制文件就是备份），运维成本几乎为零。

驱动选择 `better-sqlite3` 而非更流行的 `sqlite3`，理由很简单：**同步API更好用**。代码里直接 `db.prepare('SELECT ...').all()` 就拿到结果，不需要套回调或await。代码量少一半，可读性高一倍。**六张表的 E-R 模型和建表 SQL，是我在 WorkBuddy 辅助下反复迭代出来的。它对 SQL 语法的实时纠错，帮我省下了大量调试时间——这种「边写边纠」的体验，比我一个人闷头查文档强太多。**

## 二、E-R 关系模型总览

平台共设计6张核心表：

```
┌──────────┐       ┌──────────┐
│  roles   │──────<│  users   │
│ (角色表) │ 1:N   │ (用户表) │
└──────────┘       └────┬─────┘
                        │
                        │ N:1
                        ▼
┌──────────┐       ┌──────────┐       ┌─────────┐
│categories│──────<│ resources │──────>│ albums  │
│(分类表)  │ 1:N   │ (资源表)  │  N:1  │ (相册表)│
└────┬─────┘       └────┬─────┘       └─────────┘
     │                   │
     │ 自引用(N级子分类)   │ N:1
     │                   ▼
     │              ┌──────────┐
     └─────────────>│ reviews  │
                    │ (审核表)  │
                    └──────────┘
```

### 表关系说明

- **users → roles**：每个用户归属一个角色（多用户对一角色）
- **categories → resources**：每个资源属于一个分类（多资源对一分类）
- **categories → categories**：自引用，实现多级子分类（如「学生成绩→九年级→一班」）
- **resources → albums**：仅活动照片板块的资源关联相册（可为NULL）
- **resources → reviews**：每个审核操作产生一条审核记录（一对多）
- **resources → users**：资源上传者（外键关联）

![screenshot_03_database_er.png](https://developer.qcloudimg.com/http-save/yehe-12595092/0ec14f699f8f6c89645b5d3f53f24b7d.png)

![screenshot_12_admin_resources_clean.png](https://developer.qcloudimg.com/http-save/yehe-12595092/43e7d5d179cef4d71097746c576fb7a7.png)

![screenshot_13_admin_mapping_clean.png](https://developer.qcloudimg.com/http-save/yehe-12595092/b821159cdbfdd3fa6c8303bc6bc6392e.png)

## 三、逐表设计详解

### 1. roles — 角色表

```sql
CREATE TABLE IF NOT EXISTS roles (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        VARCHAR(30) NOT NULL UNIQUE,   -- teacher / staff / admin / superadmin
    level       INTEGER NOT NULL DEFAULT 1,    -- 权限等级 1~4（数字越大权限越高）
    description TEXT                            -- 角色描述
);
```

四级角色体系是整个权限模型的基础：

| 角色 | level | 典型人物 | 可见板块 | 核心能力 |
|------|-------|---------|---------|---------|
| teacher | 1 | 普通教师 | 教学资料+活动照片 | 上传/下载/预览 |
| staff | 2 | 教务员 | 全部四板块 | +审核/+新建文件夹 |
| admin | 3 | 管理员 | 全部四板块 | +用户管理/+系统设置 |
| superadmin | 4 | 超管 | 全部 | 全部+安全策略 |

初始数据（系统初始化时自动插入）：
```sql
INSERT INTO roles (name, level, description) VALUES
    ('teacher',     1, '普通教师——上传下载资源'),
    ('staff',       2, '教务员——审核资源+管理分类'),
    ('admin',       3, '管理员——用户管理+系统设置'),
    ('superadmin',  4, '超级管理员——全部权限');
```

### 2. users — 用户表

```sql
CREATE TABLE IF NOT EXISTS users (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    username    VARCHAR(50) NOT NULL UNIQUE,      -- 登录账号
    password    VARCHAR(200) NOT NULL,             -- bcrypt哈希（非明文）
    real_name   VARCHAR(50) NOT NULL,              -- 真实姓名（显示用）
    role_id     INTEGER NOT NULL DEFAULT 1,        -- 外键 → roles.id
    status      INTEGER NOT NULL DEFAULT 1,        -- 1=正常 0=禁用
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_role ON users(role_id);
```

**设计要点**：
- `password`字段存的是bcrypt哈希，原始密码永远不落地
- `real_name`单独存一份——因为显示名可能和登录名不同（如登录名`zhangsan`，显示名`张老师`）
- 索引：`username`用于登录查询（唯一索引自带唯一约束），`role_id`用于按角色筛选用户

### 3. categories — 分类表

```sql
CREATE TABLE IF NOT EXISTS categories (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        VARCHAR(50) NOT NULL,             -- 分类名称（如"八年级数学"）
    slug        VARCHAR(50) NOT NULL UNIQUE,       -- 英文标识（如"teaching"、"student_grade"）
    parent_id   INTEGER DEFAULT NULL,              -- 父分类ID（NULL=顶级分类）
    sort_order  INTEGER DEFAULT 0,                 -- 排序权重
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);
```

四大顶级分类及典型子分类结构：

```
教学资料 (teaching)          学校管理 (school_admin)
├── 语文                     ├── 德育处
├── 数学                     ├── 教务处
├── 英语                     ├── 总务处
├── 物理                     └── ...
└── 化学
学生成绩 (student_grade)      活动照片 (activity_photo)
├── 七年级                   ├── 2025秋季运动会
├── 八年级                   ├── 教师节表彰大会
├── 九年级                   └── ...
└── 各班级子分类
```

这个自引用的`parent_id`设计让分类树可以无限扩展——如果未来需要增加「年级→学科→章节」三级子分类，不需要改表结构。

### 4. resources — 资源表（核心表）

```sql
CREATE TABLE IF NOT EXISTS resources (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    filename        VARCHAR(255) NOT NULL,         -- 存储文件名（UUID重命名）
    original_name   VARCHAR(255) NOT NULL,          -- 原始文件名（中文）
    path            VARCHAR(500) NOT NULL,           -- 相对存储路径
    size_bytes      INTEGER NOT NULL DEFAULT 0,      -- 文件大小（字节）
    mime_type       VARCHAR(100),                   -- MIME类型
    category_id     INTEGER NOT NULL,                -- 所属分类
    sub_category    VARCHAR(100),                   -- 子分类（自由文本）
    uploader_id     INTEGER NOT NULL,                -- 上传者ID
    status          VARCHAR(20) DEFAULT 'pending',   -- pending/approved/rejected
    review_comment  TEXT DEFAULT '',                 -- 审核意见
    reviewed_at     DATETIME,                        -- 审核时间
    reviewer_id     INTEGER,                         -- 审核人ID
    album_id        INTEGER DEFAULT NULL,            -- 关联相册（活动照片用）
    download_count  INTEGER DEFAULT 0,               -- 下载次数
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    FOREIGN KEY (uploader_id) REFERENCES users(id),
    FOREIGN KEY (reviewer_id) REFERENCES users(id),
    FOREIGN KEY (album_id) REFERENCES albums(id)
);

CREATE INDEX idx_res_category ON resources(category_id, status);
CREATE INDEX idx_res_uploader ON resources(uploader_id);
CREATE INDEX idx_res_status ON resources(status);
CREATE INDEX idx_res_album ON resources(album_id);
```

**这是整个系统最核心的表**，几个关键设计决策：

**为什么filename和original_name分开存？**
- `filename`是服务器上的实际文件名（如`a1b2c3d4.pdf`），用UUID或时间戳生成，避免中文乱码和文件名冲突
- `original_name`是用户上传时的原始文件名（如`勾股定理的应用_课件.pptx`），用于前端展示和搜索

**status 字段的三态设计**：
- `pending`：刚上传，等待审核
- `approved`：审核通过，对有权限的用户可见
- `rejected`：审核被拒，只有上传者本人可见

**album_id 可为NULL**：只有活动照片板块的资源才关联相册，其他板块此字段为空。这比拆成两张表更简单，查询时加个`WHERE album_id IS NOT NULL`即可。

### 5. albums — 相册表

```sql
CREATE TABLE IF NOT EXISTS albums (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    name            VARCHAR(100) NOT NULL,          -- 相册名称（如"2025秋季运动会"）
    cover_path      VARCHAR(500),                   -- 封面图片路径
    creator_id      INTEGER NOT NULL,               -- 创建者
    photo_count     INTEGER DEFAULT 0,              -- 照片数量（冗余字段，加速列表展示）
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (creator_id) REFERENCES users(id)
);
```

相册本身只存元数据，具体的每张照片还是作为一条`resources`记录存在资源表里——通过`album_id`关联。这样做的优势是：照片既可以存在于相册中，又享受资源表的统一管理（审核、权限、预览等）。

### 6. reviews — 审核记录表

```sql
CREATE TABLE IF NOT EXISTS reviews (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    resource_id     INTEGER NOT NULL,               -- 被审资源
    reviewer_id     INTEGER NOT NULL,               -- 审核人
    action          VARCHAR(20) NOT NULL,            # approve / reject
    comment         TEXT DEFAULT '',                 -- 审核意见
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (resource_id) REFERENCES resources(id),
    FOREIGN KEY (reviewer_id) REFERENCES users(id)
);

CREATE INDEX idx_reviews_resource ON reviews(resource_id);
```

这张表的核心价值是**可追溯性**——谁在什么时间审核了哪条资源、做了什么决定、写了什么意见，全都有据可查。当老师问"我的资源为什么被拒绝了"时，可以直接查出原因。

## 四、建表初始化脚本完整版

```sql
-- ══════════════════════════════════════
--  edu-resource 数据库初始化脚本
--  首次启动时由 db.js 自动执行
-- ══════════════════════════════════════

PRAGMA journal_mode = WAL;   -- WAL模式，读写并发更好
PRAGMA foreign_keys = ON;     -- 启用外键约束

-- 以下 CREATE TABLE IF NOT EXISTS 语句...
-- （上面六张表的完整DDL，此处省略重复）
-- ...

-- 初始角色数据
INSERT OR IGNORE INTO roles (id, name, level, description) VALUES
    (1, 'teacher',     1, '普通教师'),
    (2, 'staff',       2, '教务员'),
    (3, 'admin',       3, '管理员'),
    (4, 'superadmin',  4, '超级管理员');

-- 初始分类数据
INSERT OR IGNORE INTO categories (id, name, slug, parent_id, sort_order) VALUES
    (1,  '教学资料',     'teaching',       NULL, 1),
    (2,  '学校管理',     'school_admin',   NULL, 2),
    (3,  '学生成绩',     'student_grade',  NULL, 3),
    (4,  '活动照片',     'activity_photo', NULL, 4),
    (5,  '语文',         'chinese',        1,   1),
    (6,  '数学',         'math',           1,   2),
    (7,  '英语',         'english',        1,   3),
    (8,  '物理',         'physics',        1,   4),
    (9,  '化学',         'chemistry',      1,   5),
    (10, '德育处',       'dean',           2,   1),
    (11, '教务处',       'academic',       2,   2),
    (12, '七年级',       'grade7',         3,   1),
    (13, '八年级',       'grade8',         3,   2),
    (14, '九年级',       'grade9',         3,   3);

-- 默认管理员账户（初始密码请替换为你的强密码；下方为示例哈希值，切勿使用示例明文）
INSERT OR IGNORE INTO users (username, password, real_name, role_id) VALUES
    ('admin', '$2a$10$xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', '系统管理员', 4);
```

> ⚠️ 注意：实际部署时请替换上面的bcrypt哈希值为你自己生成的。可以通过以下Node.js代码生成：
>
> ```javascript
> const bcrypt = require('bcryptjs');
> console.log(bcrypt.hashSync('你的密码', 10));
> ```

## 五、踩坑经验

### 坑1：SQLite 的 WAL 模式与 Docker Volume 的配合

**现象**：容器重启后，有时数据库出现"database is locked"错误。
**原因**：默认的journal模式下，一个写操作会锁住整个数据库文件。如果前一个请求还在写，后面的请求就被阻塞了。
**解决**：启用WAL（Write-Ahead Logging）模式：

```javascript
// db.js 初始化时
const db = require('better-sqlite3')('edu-resource.db');
db.pragma('journal_mode = WAL');  // 改为WAL模式
db.pragma('foreign_keys = ON');    // 启用外键约束
```

WAL模式下，读操作和写操作可以并发进行——读者不阻塞写者，写者也不阻塞读者。对于我们的场景（大量浏览+少量上传），性能提升明显。

### 坑2：忘记建索引导致查询越来越慢

**现象**：上线一个月后，首页资源列表加载从1秒变成5秒。
**原因**：随着数据量增长（几千条资源），没有索引的`WHERE category_id = ? AND status = ?`查询退化为全表扫描。
**解决**：给高频查询条件加联合索引（如上面的`idx_res_category`）。原则很简单：**`WHERE`、`ORDER BY`、`JOIN ON`里出现的列都要考虑索引**。

### 坑3：photo_count 冗余字段的同步问题

**albums.photo_count`是一个冗余字段（可以用`COUNT(*) FROM resources WHERE album_id = ?`实时算出）。我设计它是为了避免每次列表查询都做一次聚合计算。
**代价**：每次新增/删除照片时，必须同时更新这个字段，否则数据不一致。
**教训**：冗余字段能提升读取性能，但增加了写入复杂度。如果数据量不大（一个相册几百张照片），其实没必要做这个优化——直接COUNT就行。

## 六、本篇小结

| 表名 | 记录数级 | 核心职责 | 设计亮点 |
|------|---------|---------|---------|
| roles | 4行固定 | 角色定义 | 四级层级体系 |
| users | ~100 | 用户信息 | bcrypt密码哈希 |
| categories | ~15 | 分类树 | 自引用多级子分类 |
| resources | ~5000 | 资源元数据 | 双文件名(UUID+原名)、三态审核 |
| albums | ~20 | 相册元数据 | 轻量设计+照片计数 |
| reviews | ~3000 | 审核日志 | 完整审计追踪 |

> **下篇预告**：《从零搭建教学资源平台（四）：用户认证与JWT鉴权实战》——从注册到登录的完整流程，包括那个容易出错的问题：**auth中间件到底该导出什么？**

**标签**：#WorkBuddy开发者分享季# #数据库# #SQLite# #表设计# #后端开发# #E-R模型#


很多人对SQLite的印象还停留在"手机App的本地存储"，但实际上它的能力远超大多数人的认知：

# 从零搭建教学资源平台（三）：数据库表结构设计 #WorkBuddy开发者分享季#

系列导航：本文是系列第三篇。前一篇讲了技术选型和架构，本篇深入数据库层——六张表的设计思路、E-R关系和建表SQL。

数据库

后端

开发工具

SQLite数据库在生产环境中表现优异，支持TB级数据、完整ACID事务，适合20-50人并发场景。文章详细解析了教学资源平台的6张核心表设计，包括用户权限、分类管理、资源审核等模块，采用自引用、冗余字段等优化技巧，并分享了SQLite的WAL模式、索引优化等实战经验。

代码解释

中间件

服务器

WorkBuddy 

容器

SQLite

MySQL

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

# 从零搭建教学资源平台（三）：数据库表结构设计 #WorkBuddy开发者分享季#-腾讯云开发者社区-腾讯云

# 从零搭建教学资源平台（三）：数据库表结构设计 #WorkBuddy开发者分享季#

# 从零搭建教学资源平台（三）：数据库表结构设计 #WorkBuddy开发者分享季#

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐