数据库原理与选型 / 系统课时
项目需求分析与数据建模
项目需求分析与数据建模
本节把“笔记可以拥有多个标签、标签也能关联多篇笔记”转成可落地的 SQLite 模型。完成后,你会得到一组包含主键、唯一约束、外键和常用查询索引的表结构,并能用查询验证模型是否支持核心需求。
学习目标
- 从业务动作识别实体、字段和多对多关系
- 为笔记与标签设计主键、唯一约束和外键
- 用实际查询验证模型是否满足检索需求
从业务问题反推数据结构
先写需求,不要先写表。这个小项目至少要支持:创建笔记、给笔记添加多个标签、按标签找笔记、查看一篇笔记的全部标签,以及删除笔记时清理无效关联。
因此可以得到三个实体:notes 保存笔记,tags 保存标签,note_tags 保存二者的多对多关系。标签名称应该唯一,否则“SQLite”和“sqlite”以外的同名标签可能被重复创建;关联表则必须阻止同一笔记重复绑定同一标签。

建立最小可用模型
下面的 SQL 可以直接在一个空数据库中执行。INTEGER PRIMARY KEY 让主键与 SQLite 的 rowid 机制自然配合;关联表使用联合主键,既表达关系身份,也避免重复绑定。
PRAGMA foreign_keys = ON;
CREATE TABLE notes (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
content TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE tags (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE note_tags (
note_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
PRIMARY KEY (note_id, tag_id),
FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
CREATE INDEX idx_note_tags_tag_id ON note_tags(tag_id);
INSERT INTO notes (title, content) VALUES
('SQLite 索引', '记录索引设计要点'),
('事务排查', '记录锁冲突处理步骤');
INSERT INTO tags (name) VALUES ('SQLite'), ('数据库');
INSERT INTO note_tags (note_id, tag_id) VALUES
(1, 1),
(1, 2),
(2, 1);
SELECT n.id, n.title
FROM notes AS n
JOIN note_tags AS nt ON nt.note_id = n.id
JOIN tags AS t ON t.id = nt.tag_id
WHERE t.name = 'SQLite'
ORDER BY n.id;预期输出
1|SQLite 索引
2|事务排查如果使用图形化客户端,列分隔符可能不同,但应返回相同的两条记录。
让约束替应用代码兜底
NOT NULL 保证关键字段存在,UNIQUE 保证标签名不重复,联合主键保证一条关系只有一个实例。外键的 ON DELETE CASCADE 则让删除笔记或标签时自动清理关联表,避免遗留孤儿记录。
要注意,SQLite 连接默认不一定启用外键检查,因此应用建立连接后应执行 PRAGMA foreign_keys = ON,并通过 PRAGMA foreign_keys 验证结果为 1。不要只定义 FOREIGN KEY 就假设约束一定会执行。
用查询需求决定索引
联合主键 (note_id, tag_id) 已经适合“按笔记找标签”,因为查询可以从主键左侧的 note_id 开始匹配。反过来“按标签找笔记”以 tag_id 为入口,所以额外建立 idx_note_tags_tag_id。
索引不是字段越多越好。这个项目先围绕真实查询路径建最少索引;等出现按创建时间排序、全文搜索或标题模糊匹配等新需求,再根据查询计划决定是否新增索引。
这个模型的关键不是把所有未来字段一次性设计完,而是让实体、关系、约束和查询入口形成闭环:业务规则能由数据库约束表达,核心查询有明确路径,删除操作不会破坏关系完整性。