SQLite数据库最优设计方案咨询：多表结构选择与简化优化方法

核心结论：推荐保留3张独立表，补充用户基础表可进一步提升可维护性 #

为什么不建议合并为2张表 #

• 3张表属于完全独立的业务域，合并会违反数据库设计第三范式，带来冗余和后期维护问题：
  • 图片存储表是全局资源表，和用户业务完全无关，独立存储可以避免blob类型的标签字段拖慢用户相关数据的查询效率
  • trivia答题得分表是单一游戏的专属业务数据，和通用游戏记录表的使用场景完全分离，合并后如果后续新增其他游戏的专属字段，表结构会快速膨胀，数据一致性校验成本陡增
• 两张用户相关表主键一致仅代表关联的用户主体相同，不代表数据可以合并，分开存储后查询特定业务数据时只需要扫描对应表的字段，性能远高于查询合并后的宽表

可落地的优化方案（兼顾简化和扩展性） #

你目前的设计隐含了一个缺失的核心表：用户基础表，补充后整体结构更严谨，也能避免后续重复存储用户公共属性：

1. 用户基础表（新增，存储所有用户通用属性）

CREATE TABLE user (
  user_id INTEGER PRIMARY KEY AUTOINCREMENT,
  create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  -- 可按需扩展昵称、头像url等通用用户字段
);

2. 图片存储表（原有，无需改动）

CREATE TABLE image (
  url TEXT PRIMARY KEY,
  tags BLOB NOT NULL
);

可选优化：若后续需要对图片标签进行检索查询，可将BLOB类型的tags字段改为JSON类型，SQLite 3.9.0及以上版本原生支持JSON字段的索引和查询操作，比BLOB更灵活。

3. trivia答题得分表（原有，主键改为关联user_id的外键）

CREATE TABLE trivia_score (
  user_id INTEGER PRIMARY KEY,
  total_score INTEGER NOT NULL DEFAULT 0,
  correct_count INTEGER NOT NULL DEFAULT 0,
  wrong_count INTEGER NOT NULL DEFAULT 0,
  rank INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);

4. 游戏记录表（新增，主键关联user_id的外键）

CREATE TABLE game_record (
  user_id INTEGER PRIMARY KEY,
  level INTEGER NOT NULL DEFAULT 1,
  win_count INTEGER NOT NULL DEFAULT 0,
  lose_count INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE CASCADE
);

如果你的业务规模极小、未来无新增游戏类型的计划，也可以把trivia_score的字段直接合并到game_record表中，最终保留3张表（用户表+图片表+游戏数据表），进一步减少表数量，代价是后续新增其他游戏时需要修改表结构。 #

极端简化方案（仅适用于个人Demo级项目） #

如果完全不需要考虑扩展性，你可以直接把trivia相关字段合并到游戏记录表，和图片表一共2张表，不推荐生产环境使用，会导致后续业务迭代效率极低。

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