SQLite单条任务行关联多条评论的最优表结构设计咨询

最优表结构设计方案 #

你需要拆分原有单字段存储评论的结构，改为一对多关系表设计，完全不用预设评论数量上限，具体调整如下：

1. 调整现有tasks表 #

首先给原有tasks表新增自增主键task_id作为每条任务的唯一标识，原有字段保留，删除原有的comment字段（如果历史单条评论需要保留，可后续迁移到新的评论表中）。
建表语句参考：

CREATE TABLE IF NOT EXISTS tasks (
    task_id INTEGER PRIMARY KEY AUTOINCREMENT,
    date TEXT NOT NULL,
    time TEXT NOT NULL,
    project TEXT NOT NULL,
    task TEXT NOT NULL,
    owner TEXT NOT NULL
);

2. 新建独立的comments评论表 #

单独建表存储所有评论，通过外键关联到所属任务：

CREATE TABLE IF NOT EXISTS comments (
    comment_id INTEGER PRIMARY KEY AUTOINCREMENT,
    task_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    create_time TEXT NOT NULL DEFAULT (datetime('now','localtime')),
    -- 可选拓展字段：比如评论人、修改时间等
    FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE
);

注意：SQLite默认关闭外键约束，每次建立数据库连接后需要先执行 PRAGMA foreign_keys = ON; 开启，保证关联数据一致性。上面的外键配置ON DELETE CASCADE会在删除任务时自动删除所有关联的评论，你也可以根据业务需求调整为其他约束动作。

多评论关联实现逻辑 #

无需提前预知评论最大数量，关联查询逻辑如下：

• 任务列表页查询：先查询所有任务数据，拿到所有任务的task_id集合，再批量查询comments表中所有task_id在该集合内的评论，按task_id分组后依次挂载到对应任务对象下即可。
• 单任务评论查询：直接执行语句 SELECT * FROM comments WHERE task_id = ? ORDER BY create_time ASC; 即可拿到该任务下按提交时间正序排列的所有评论，直接渲染到任务条目下方即可。

如果需要保留原有tasks表中comment字段的历史数据，执行一次批量插入即可完成迁移：

INSERT INTO comments (task_id, content) SELECT task_id, comment FROM tasks WHERE comment IS NOT NULL AND comment != '';

迁移完成后即可删除tasks表的comment字段，避免数据冗余。

内容的提问来源于stack exchange，提问作者mrbc42