数据库原理与选型 / 系统课时

项目需求分析与数据建模

项目需求分析与数据建模

本节把“笔记可以拥有多个标签、标签也能关联多篇笔记”转成可落地的 SQLite 模型。完成后,你会得到一组包含主键、唯一约束、外键和常用查询索引的表结构,并能用查询验证模型是否支持核心需求。

学习目标

  • 从业务动作识别实体、字段和多对多关系
  • 为笔记与标签设计主键、唯一约束和外键
  • 用实际查询验证模型是否满足检索需求

从业务问题反推数据结构

先写需求,不要先写表。这个小项目至少要支持:创建笔记、给笔记添加多个标签、按标签找笔记、查看一篇笔记的全部标签,以及删除笔记时清理无效关联。

因此可以得到三个实体:notes 保存笔记,tags 保存标签,note_tags 保存二者的多对多关系。标签名称应该唯一,否则“SQLite”和“sqlite”以外的同名标签可能被重复创建;关联表则必须阻止同一笔记重复绑定同一标签。

笔记、标签与关联表的数据模型

建立最小可用模型

下面的 SQL 可以直接在一个空数据库中执行。INTEGER PRIMARY KEY 让主键与 SQLite 的 rowid 机制自然配合;关联表使用联合主键,既表达关系身份,也避免重复绑定。

sql
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;

预期输出

text
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。

索引不是字段越多越好。这个项目先围绕真实查询路径建最少索引;等出现按创建时间排序、全文搜索或标题模糊匹配等新需求,再根据查询计划决定是否新增索引。

这个模型的关键不是把所有未来字段一次性设计完,而是让实体、关系、约束和查询入口形成闭环:业务规则能由数据库约束表达,核心查询有明确路径,删除操作不会破坏关系完整性。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

项目需求分析与数据建模

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通