如何在SQL中存储重复行数据（JSON）

本文旨在解决如何在postgresql数据库中使用Prisma进行开发时，有效地存储包含重复行数据的场景。通常，这种场景出现在需要将多个相关联的数据项（例如演员及其角色）存储在一个记录中。虽然可以使用jsonB数据类型将数据存储为json数组，但这不是最佳实践，尤其是在需要对数据进行复杂查询时。本文将介绍一种更有效、更易于维护的方法：使用多对多关系表。

使用多对多关系表

当需要将多个 talent (演员) 与一个 cast (剧组) 关联，并且每个关联还需要存储额外的信息（如 role 和 comment）时，最合适的解决方案是创建一个多对多关系表。这种方法避免了在单个记录中存储JSON数组，从而提高了查询效率和数据一致性。

假设我们有三个表：cast、talent 和 cast_talent。

• cast 表存储剧组的基本信息，例如创建者、项目名称、评论和共享对象。
• talent 表存储演员的基本信息，例如 ID 和其他相关属性。
• cast_talent 表是一个连接 cast 和 talent 的关系表，用于存储每个演员在特定剧组中的角色和评论。

以下是每个表的结构示例：

-- cast 表 CREATE TABLE cast (   id SERIAL PRIMARY KEY,   createdby VARCHAR(255),   project VARCHAR(255),   comment TEXT,   shared_with VARCHAR(255) );  -- talent 表 CREATE TABLE talent (   id SERIAL PRIMARY KEY,   name VARCHAR(255),   -- 其他 talent 相关属性   ... );  -- cast_talent 表 CREATE TABLE cast_talent (   talent_id INTEGER REFERENCES talent(id),   cast_id INTEGER REFERENCES cast(id),   role VARCHAR(255),   comment TEXT,   PRIMARY KEY (talent_id, cast_id) -- 联合主键 );

说明:

• REFERENCES 关键字用于创建外键约束，确保 cast_talent 表中的 talent_id 和 cast_id 引用 talent 表和 cast 表中存在的 ID。
• PRIMARY KEY (talent_id, cast_id) 定义了一个联合主键，确保每个演员在每个剧组中只能有一个角色和评论。

示例数据

假设我们有一个剧组（cast），ID为 1，以及两个演员（talent），ID分别为 1 和 2。我们可以向 cast_talent 表中插入数据，表示这两个演员都参与了这个剧组，并记录他们的角色和评论。

-- 演员 1 (talent_id = 1) 在剧组 1 (cast_id = 1) 中扮演主角，并有相关评论 INSERT INTO cast_talent (talent_id, cast_id, role, comment) VALUES (1, 1, '主角', '表现出色');  -- 演员 2 (talent_id = 2) 在剧组 1 (cast_id = 1) 中扮演配角，并有相关评论 INSERT INTO cast_talent (talent_id, cast_id, role, comment) VALUES (2, 1, '配角', '需要更多练习');

优势

• 查询效率高： 可以使用 SQL 查询轻松地检索特定演员参与的剧组，或者特定剧组中的所有演员及其角色信息。
• 数据一致性： 外键约束确保数据的一致性，防止出现无效的关联。
• 易于维护： 表结构清晰，易于理解和维护。
• 可扩展性： 可以轻松地添加新的属性到 cast_talent 表中，例如角色的重要性或出场时间。

注意事项

• 在设计数据库时，需要仔细考虑表之间的关系，以确保数据的完整性和一致性。
• 根据实际需求，可以对表结构进行调整，例如添加索引以提高查询效率。
• 使用 Prisma 进行数据库操作时，需要定义相应的模型和关系，以便进行类型安全的查询和更新。

总结

虽然使用 JSONB 数据类型在单个记录中存储重复行数据是一种可行的方案，但使用多对多关系表通常是更好的选择，尤其是在需要对数据进行复杂查询时。多对多关系表提供了更高的查询效率、数据一致性和可维护性，是存储关联数据的推荐方法。通过合理地设计表结构和使用外键约束，可以确保数据的完整性和一致性，从而构建更健壮和可扩展的应用程序。