如何用sqflite为嵌套模型创建独立关联表，无需扁平化或转JSON/字符串

方案核心是通过外键关联三张独立表，封装事务保证嵌套数据插入的一致性，完全不需要将嵌套数据转JSON或者扁平化存储。

第一步：定义三张表的结构 #

三张表分别对应Collection、Translations、Question实体，通过外键建立关联，sqflite 本身不支持布尔类型，统一用0/1存储布尔值：

-- 开启外键支持，需要在数据库初始化时执行
PRAGMA foreign_keys = ON;

-- 1. 翻译表：存储所有多语言内容
CREATE TABLE translations (
  id TEXT PRIMARY KEY,
  zh_cn TEXT,
  en_us TEXT,
  -- 其他你需要的语言字段按需补充
);

-- 2. 问题表：和Collection是一对多关系，通过collection_id关联
CREATE TABLE questions (
  id TEXT PRIMARY KEY,
  collection_id TEXT NOT NULL,
  -- 你Question实体的其他字段，比如content、type等按需补充
  FOREIGN KEY (collection_id) REFERENCES collections(id) ON DELETE CASCADE
);

-- 3. 集合表：通过name_id、description_id关联翻译表
CREATE TABLE collections (
  id TEXT PRIMARY KEY,
  workspace_code TEXT,
  code TEXT,
  author TEXT,
  modifier TEXT,
  creation_time TEXT,
  modification_time TEXT,
  unit_code TEXT,
  shared INTEGER,
  version INTEGER,
  name_id TEXT,
  description_id TEXT,
  resource_type TEXT,
  FOREIGN KEY (name_id) REFERENCES translations(id) ON DELETE CASCADE,
  FOREIGN KEY (description_id) REFERENCES translations(id) ON DELETE CASCADE
);

-- 如果permissions属性也不想转JSON存储，可以额外建关联表
CREATE TABLE collection_permissions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  collection_id TEXT NOT NULL,
  permission TEXT NOT NULL,
  FOREIGN KEY (collection_id) REFERENCES collections(id) ON DELETE CASCADE
);

ON DELETE CASCADE配置的作用是删除Collection时，自动删除关联的翻译、问题、权限数据，不需要手动清理冗余数据。

第二步：封装插入逻辑，事务保证一致性 #

所有嵌套数据的插入操作放在同一个事务中执行，只要有一步失败就全部回滚，避免出现脏数据：

import 'package:uuid/uuid.dart';

Future<void> insertCollection(Collection collection) async {
  final db = await yourDatabaseInstance; // 替换为你自己的sqflite实例获取逻辑
  await db.transaction((txn) async {
    // 1. 插入name对应的翻译记录，生成唯一id
    final nameId = const Uuid().v4();
    await txn.insert('translations', {
      'id': nameId,
      'zh_cn': collection.name?.zhCn,
      'en_us': collection.name?.enUs,
      // 其他语言字段对应赋值
    });

    // 2. 插入description对应的翻译记录，生成唯一id
    final descId = const Uuid().v4();
    await txn.insert('translations', {
      'id': descId,
      'zh_cn': collection.description?.zhCn,
      'en_us': collection.description?.enUs,
    });

    // 3. 插入Collection主表数据
    await txn.insert('collections', {
      'id': collection.id,
      'workspace_code': collection.workspaceCode,
      'code': collection.code,
      'author': collection.author,
      'modifier': collection.modifier,
      'creation_time': collection.creationTime,
      'modification_time': collection.modificationTime,
      'unit_code': collection.unitCode,
      'shared': collection.shared == true ? 1 : 0,
      'version': collection.version,
      'name_id': nameId,
      'description_id': descId,
      'resource_type': collection.resourceType,
    });

    // 4. 批量插入关联的Question列表
    if (collection.question != null && collection.question!.isNotEmpty) {
      for (final q in collection.question!) {
        await txn.insert('questions', {
          'id': q.id,
          'collection_id': collection.id,
          // Question其他字段对应赋值
        });
      }
    }

    // 5. 批量插入关联的权限列表（如果用了独立权限表）
    if (collection.permissions != null && collection.permissions!.isNotEmpty) {
      for (final p in collection.permissions!) {
        await txn.insert('collection_permissions', {
          'collection_id': collection.id,
          'permission': p,
        });
      }
    }
  });
}

第三步：查询自动组装嵌套实体 #

查询时通过多表联查或者分表查询，把关联数据直接组装成你需要的嵌套结构即可：

Future<Collection?> getCollectionById(String collectionId) async {
  final db = await yourDatabaseInstance;
  // 联查Collection主表和两个翻译表的内容
  final collectionResult = await db.rawQuery('''
    SELECT 
      c.*,
      t1.zh_cn as name_zh, t1.en_us as name_en,
      t2.zh_cn as desc_zh, t2.en_us as desc_en
    FROM collections c
    LEFT JOIN translations t1 ON c.name_id = t1.id
    LEFT JOIN translations t2 ON c.description_id = t2.id
    WHERE c.id = ?
  ''', [collectionId]);

  if (collectionResult.isEmpty) return null;
  final baseData = collectionResult.first;

  // 查询关联的Question列表
  final questionResult = await db.query(
    'questions',
    where: 'collection_id = ?',
    whereArgs: [collectionId],
  );
  final questions = questionResult.map((q) => Question.fromMap(q)).toList();

  // 查询关联的权限列表
  final permissionResult = await db.query(
    'collection_permissions',
    where: 'collection_id = ?',
    whereArgs: [collectionId],
  );
  final permissions = permissionResult.map((p) => p['permission'] as String).toList();

  // 组装为嵌套的Collection实体
  return Collection(
    id: baseData['id'] as String,
    workspaceCode: baseData['workspace_code'] as String?,
    code: baseData['code'] as String?,
    author: baseData['author'] as String?,
    modifier: baseData['modifier'] as String?,
    creationTime: baseData['creation_time'] as String?,
    modificationTime: baseData['modification_time'] as String?,
    unitCode: baseData['unit_code'] as String?,
    shared: baseData['shared'] == 1,
    version: baseData['version'] as int?,
    name: Translations(
      zhCn: baseData['name_zh'] as String?,
      enUs: baseData['name_en'] as String?,
    ),
    description: Translations(
      zhCn: baseData['desc_zh'] as String?,
      enUs: baseData['desc_en'] as String?,
    ),
    question: questions,
    permissions: permissions,
    resourceType: baseData['resource_type'] as String?,
  );
}

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