postgresql - 如何避免引用系统中用户编辑的重复记录？

Question

PostgreSQL 10.1

我很好奇 SO 社区如何处理这个常见的数据库问题。

问题是这样的。我在桌面上将各种问题的文字描述输入到 ICD_Descriptions 表(下面的中间框)中。一般来说，每个描述也会有一个代码。但是，随着时间的推移(即数年)，特定文本短语/描述的代码会发生变化。因此，对于某些代码到描述，将存在一般的多对多关系。因此，第三个表 dx_log 用于允许代码和描述之间的多对多关系。最后，其他需要查看“代码描述”特定组合的“子”表将被提供对 dx_log 的主键(recid)的引用。我相信这种安排是相当标准的数据库管理。

好的，现在开始讨论问题。我希望 icd_code 表中的代码对该表是唯一的。我也希望 icd_description 表中的描述对于他们的表是唯一的。

问题。这是一个引用系统，对代码表的“数据”部分(代码)或描述(在描述表中)的更改将在子表中看到。

但是，如何正确管理用户对与相应表的“唯一”规则冲突的代码或描述的编辑？

例如，假设描述的初始文本是“this is mispelled”，而另一个描述文本是“this is spelled”。在某一时刻，这两个短语共存并且是独一无二的。然而，在稍后的某个时间点，错误的记录被更正(即，拼错--->拼错)。当尝试将编辑保存到文件时，将检测到正确的记录已经存在。

因此，如果 dx_log 表在 (icd_description_recid, icd_code_recid) 上也是唯一的，那么简单地替换对 dx_log 中已经存在的正确拼写记录的引用将导致违反 dx_log 表的唯一性。 因此，我只能想到三种解决方案:

1. 子表实际引用的表在其引用指针上不能有唯一约束，
2. 在 dx_log 上保留唯一性约束，但是当冲突违反唯一性时，然后使用迁移过程将子表引用移动到现有记录(在 postgresql 中这将大量使用目录)到“新记录”，然后在添加新记录之前删除现有记录。
3. 在 dx_log 中添加一个额外的自引用指针，这样当一条记录与 dx_log 中已经存在的记录冲突时，不要更改它，而是放置一个指向已经存在的“正确”记录的指针 在 dx_log 中。

我希望我已经充分解释了我的问题。推荐的方法是什么？

感谢任何评论。

Answer 1

我会说 2. 是正确的解决方案。

是的，当合并两个条目时，这要求必须更新 dx_log 中的所有相关记录，但无论如何你在 dx_log(icd_description_recid) 上有一个索引，对吧？

其他解决方案会损害数据一致性，并会使系统上的所有查询变得更加复杂并且可能更慢。