待办事项（Todo）应用几乎是每个学习移动端或桌面开发的程序员都会做的小项目，但要把它的数据库设计得既简单又可扩展，其实比想象中考验功力。SQLite作为嵌入式数据库，不需要独立服务进程，一个文件就是一个库，非常适合这类本地优先的应用。这篇文章从最简单的表结构出发，一步步讨论分类、标签、子任务、重复任务等需求的建模方式，并给出索引和查询层面的实践建议。
一、从最小可用的单表结构开始
最简单的待办事项只需要一张表。下面是一个基础版本的建表语句：
CREATE TABLE todo (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
note TEXT DEFAULT '',
is_done INTEGER NOT NULL DEFAULT 0,
due_date TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime')),
updated_at TEXT NOT NULL DEFAULT (datetime('now', 'localtime'))
);
有几个细节值得注意。id使用了INTEGER PRIMARY KEY，在SQLite中它会自动成为rowid的别名，插入时传NULL即可自动递增，性能比手动维护UUID好得多。is_done用INTEGER存0和1，因为SQLite没有原生的布尔类型，这是一种约定俗成的写法。
时间字段用TEXT存储是SQLite的常见做法，格式遵循ISO 8601（例如2024-05-01 09:30:00），这样字符串比较的大小关系和时间先后顺序是一致的，排序和范围查询都直接可用。如果需要毫秒精度，也可以用REAL存Unix时间戳，两种方案都可行，关键是整个项目保持统一，不要混用。
这个单表版本适合原型验证，但一旦用户提出“我想给任务分组”“我想打标签”这类需求，就必须引入新的表了。
二、引入分类、标签与子任务
分类（Category）和待办事项是一对多关系，单独建一张表即可：
CREATE TABLE category (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE,
color TEXT DEFAULT '#CCCCCC'
);
-- 在todo表上补充外键
ALTER TABLE todo ADD COLUMN category_id INTEGER REFERENCES category(id) ON DELETE SET NULL;
注意SQLite默认不开启外键约束，需要执行PRAGMA foreign_keys = ON;才会真正生效，每次建立连接后都要设置一次。上面的ON DELETE SET NULL表示分类被删除时，其下任务的分类字段置空而不是连带删除任务，这对用户数据更友好。
标签（Tag）和待办事项是多对多关系，需要一张中间表：
CREATE TABLE tag (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE todo_tag (
todo_id INTEGER NOT NULL REFERENCES todo(id) ON DELETE CASCADE,
tag_id INTEGER NOT NULL REFERENCES tag(id) ON DELETE CASCADE,
PRIMARY KEY (todo_id, tag_id)
);
中间表用复合主键防止重复打标，两个外键都设置级联删除，删任务时关联记录自动清理。查询“带有某标签的所有未完成任务”可以这样写：
SELECT t.* FROM todo t JOIN todo_tag tt ON tt.todo_id = t.id JOIN tag g ON g.id = tt.tag_id WHERE g.name = '工作' AND t.is_done = 0 ORDER BY t.due_date IS NULL, t.due_date;
这里的ORDER BY t.due_date IS NULL, t.due_date是个实用技巧：没有截止日期的任务会被排到最后，而不是让NULL值干扰排序。
子任务有两种建模方式。第一种是自引用，即给todo表加一个parent_id字段指向父任务；第二种是单独建subtask表。自引用方案让子任务和主任务共享同一套字段，查询“某任务及其全部子任务”只需一次递归查询：
WITH RECURSIVE sub(task_id) AS (
SELECT id FROM todo WHERE id = 5
UNION ALL
SELECT t.id FROM todo t JOIN sub s ON t.parent_id = s.task_id
)
SELECT * FROM todo WHERE id IN (SELECT task_id FROM sub);
不过自引用在层数过深时维护成本会上升。如果你的子任务只是简单的勾选项，字段明显少于主任务（比如不需要截止日期、不需要提醒），那么单独建一张轻量的subtask表会更清晰，两张表各司其职，也避免了todo表充斥大量NULL列。
三、重复任务与提醒时间的设计
“每周一早上九点提醒”这类重复规则是待办应用里最容易设计错的部分。常见误区是预先批量生成未来几个月的实例，这样做会让数据库膨胀，而且用户一旦修改规则，已生成的实例就全部作废了。更好的做法是只存一份规则定义，按需生成下一次实例。
CREATE TABLE recurrence (
todo_id INTEGER NOT NULL REFERENCES todo(id) ON DELETE CASCADE,
freq TEXT NOT NULL CHECK (freq IN ('DAILY','WEEKLY','MONTHLY','YEARLY')),
interval INTEGER NOT NULL DEFAULT 1, -- 每 interval 个周期重复一次
by_weekday TEXT, -- 如 '1,3,5' 表示周一三五
next_run TEXT NOT NULL -- 下一次应生成的日期
);
业务逻辑是：应用启动或每天定时检查next_run，到了时间就把该待办复制成一条带具体日期的记录，然后计算出新的next_run写回去。这样规则只有一个真相来源，历史记录又是独立快照，改规则不影响已完成的任务。
提醒时间建议和截止日期分开存储。due_date是“这件事什么时候到期”，remind_at是“什么时候弹通知”，两者经常不同。如果一条任务需要多个提醒（提前一天、提前一小时），再加一张remind表即可。同时要处理“时区”问题：本地存localtime便于用户阅读，但跨设备同步时应统一转成UTC，否则同一份数据在不同时区的设备上会显示不同的时间。
四、索引、事务与软删除
索引设计要从实际查询出发。待办应用最高频的查询是“列出未完成任务按日期排序”，因此下面的复合索引是首选：
CREATE INDEX idx_todo_status_date ON todo(is_done, due_date); CREATE INDEX idx_todo_category ON todo(category_id); CREATE INDEX idx_todo_tag ON todo_tag(tag_id);
idx_todo_status_date把过滤列和排序列放在一个索引里，SQLite可以直接利用索引完成过滤加排序，避免额外的排序步骤。中间表上给tag_id单独建索引，是因为复合主键(todo_id, tag_id)只对todo_id前缀有效，反查“某标签下的任务”用不上这个主键。
批量操作一定要包在事务里。比如“清空已完成的任务并删除其标签关联”，两条DELETE语句分别执行意味着两次磁盘事务，SQLite的事务提交涉及fsync，频繁的小事务是性能杀手：
BEGIN IMMEDIATE; DELETE FROM todo_tag WHERE todo_id IN (SELECT id FROM todo WHERE is_done = 1); DELETE FROM todo WHERE is_done = 1; COMMIT;
最后谈一下软删除。直接物理删除任务虽然干净，但用户误删后无法恢复，也不利于做“最近删除”功能。常见的做法是加deleted_at TEXT列，为NULL表示有效，非NULL记录删除时间，所有常规查询都要加上WHERE deleted_at IS NULL条件。如果嫌每个查询都要带条件容易遗漏，可以借助SQLite 3.37+支持的生成列配合部分索引来简化：
CREATE INDEX idx_todo_active ON todo(is_done, due_date) WHERE deleted_at IS NULL;
部分索引只覆盖未删除的行，索引更小，查询更快。定期清理方面，可以设置一个策略，比如软删除超过30天的记录在应用启动时物理删除，兼顾可恢复性与数据库体积。
总结一下设计要点：主键用INTEGER自增，时间用TEXT存ISO格式，一对多用外键，多对多建中间表，重复规则只存定义按需实例化，高频查询配复合索引，批量写操作包事务，删除走软删除。遵循这些原则，你的待办应用数据库在数据量涨到几十万条时依然能保持流畅响应。