design-patterns - 合并数据的数据库架构设计模式

Question

我们有一个过时的数据库，其中包含大量个人以及他们已经完成的许多成就。从历史上来看，几乎没有做过阻止重复的单个数据的事情，因此我们最终陷入了数据非常脏的情况。可以在here上找到其简化版本。

现在，我们正在重新设计架构和用户界面。我们将为用户提供将其个人合并在一起的工具。在所提供的示例中，Dave和David显然是同一个人，总共取得了4项成就。

考虑到用户会犯错误，并且涉及的表比示例中的要多，我正在寻找一种模式设计，该模式设计可以简化数据的合并，尤其是在(不可避免的)用户(当!)时取消数据的合并。犯了一个错误。

某种形式的链接列表似乎是一种解决方案，但对于该用例而言并非完全有效。还有其他概念可能适合这种情况吗？任何合适的特定设计模式？

编辑:由于今天的SQLFiddle非常脆弱，这是sqlfiddle上的create/insert/select sql:

CREATE TABLE individual
    (`individual_id` int, `forename` varchar(50), `surname` varchar(50))
;

CREATE TABLE achievement
    (`achievement_id` int, `name` varchar(50), `description` varchar(50))
;

CREATE TABLE individual_achievement
    (`individual_id` int,`achievement_id` int)
;

INSERT INTO individual
    (`individual_id`, `forename`, `surname`)
VALUES
    (1, 'Dave', 'Deane'),
    (2, 'David', 'Deane')
;

INSERT INTO achievement
    (`achievement_id`, `name`, `description`)
VALUES
    (1, 'unit_1', 'Unit 1'),
    (2, 'unit_2', 'Unit 2'),
    (3, 'unit_3', 'Unit 3'),
    (4, 'unit_4', 'Unit 4')
;

INSERT INTO individual_achievement
    (`individual_id`,`achievement_id`)
VALUES
    (1, 1),
    (1, 3),
    (2, 2),
    (2, 4)
;

select * from individual i
join individual_achievement ai using (individual_id)
join achievement a using (achievement_id)

编辑2:刚刚找到了这个 very similar question，希望在4年后也可以找到其他解决方案。

Answer 1

这是您可以使用的一种策略。

首先，创建一个新表，现在将其称为“Individual_v2”，其列与原始表Individual完全相同。 (理想情况下，您最终将用此表替换“个人”；实际上，人们可能仍会在“个人”中输入数据，并且您必须通过将数据移动或合并到“Individual_v2”中来“清理”数据。)使用指向“成就”的链接配置此表。 (目前，我假设成就是干净的。)

然后，创建一个“映射”表，如下所示:

IndividualMapping

OldIndividual_Id
NewIndividual_Id
CreatedAt
CreatedBy
ApprovedAt  --  Nullable!
ApprovedBy  --  Nullable!

“已创建”列用于确定何时以及由谁(或什么)创建映射。

“已批准”列用于确定数据是否已迁移到新表。

对于每个“旧”项目，您都可以确定它在"new"表中的映射位置；如果它没有映射到现有项目，则在新表中为其创建一个。

然后，在映射表中添加一个条目。如果创建了新项目，则将其标记为已批准；否则，将其标记为已批准。如果置信度很高，则将其标记为已批准；否则，将其保留为“未批准”并等待审核。在适当的时候，审阅者会仔细检查并批准映射，将映射更改为其他现有的新项目，或者创建另一个新项目并映射到该项目。

完成后，将针对新表完成“实际”工作。旧表和映射表可用于标识新数据的来源，并在必要时撤消/更改映射。

这里有很多 Unresolved 实现和支持问题，总的来说，这似乎很尴尬。从长远来看，一旦解决了重复数据的问题，您可以删除旧的(和映射)表，但是在那之前，您将拥有一个繁琐的系统。

附录

我在这里只是在讨论一些事情，而没有进行详尽的分析。我认为您正在描述的系统将是繁琐的工作，并且在概念上非常复杂，即使表格相对简单，并且最终细节不在SO问题的范围之内。同样，很大程度上取决于系统及其重新设计的总体目标和目的。我将在这里做一些假设:

“现有”系统将保留在原位

如此输入的个人(及其奖励)必须像以往一样立即可用。

将继续输入重复项；是否，何时以及在可行时将它们与现有条目

“合并”

以这种方式完成后，系统将按以下方式工作:

在Personals_v2和Achievement之间有一个单独的关系表(目前，Individual_Achievement_v2，尽管必须有一个更好的名称)。

“v2”表中的数据正确，正确和正确。 “v1”表是分段，历史记录，日志数据。

准备一个初始版本，其中在v2表中配置v1表中的所有条目。如果在此步骤中可以合并行，那就更好了。一切都记录在“map”表中，以便可以干净地返回并在必要时重做。

从此发行版开始，将在v1表中输入新数据，并同时/立即在v2表中输入新数据。如果可以映射到现有项目，请这样做，否则在v2表中创建一个新条目。始终将事件记录在“ map ”表中。

展望 future ，所有“实时”查询都将打入v2表。 v1表是(再次)历史记录，日志，审计跟踪。一旦填充，它们将永远不会被修改，而v2表(包括映射表)可以并且将会被修改。

由企业确定，将对数据进行定期检查/检查，以查找并修复随着时间的推移出现的重复条目以及“无效重复”(错误的映射)。这是当您执行回滚/重做工作时，如在映射表和v1表中所跟踪的。

您可能需要一些额外的日志记录表来跟踪诸如“通过xx/xx/xxxx输入的所有数据均有效，此后输入的数据必须进行审核”之类的内容。我敢肯定还有其他问题和微妙之处会浮现出来，它们总是会…