是否有合并重复数据库记录的设计模式？ [英] Is there a design pattern for merging duplicate database records?

问题描述

例如，假设我有一个电影迷的社交网站。有些人将洛基列为他们最喜爱的电影，其他人列出洛基1，其他还有洛基我。显而易见的是将三者合并在一起并更新关联的表。然而，对于每一个明显的解决方案，设计模式都是1）更复杂，2）有一些额外的好处。是否有合并重复数据库记录的设计模式？具体来说，提供可审计性或可逆性的东西？

解决方案

一旦你说可逆性，我认为命令模式。

典型的例子是支持撤消风格的行为，但我认为这也是一个很好的可审计性 - 特别是作为个别的步骤（对于一个更好的词）是如此之小，容易表达（例如 {合并Rocky I - >Rocky} ）。

如何让命令模式实际上用于您的场景？

嗯，在RDBMS竞技场中，而不是OO建模，假设你已经有了 USER_FAVORITE 和 MOVIE ，我将添加一个新表 USER_FAVORITE_MOVIE_MERGE_COMMAND 列：

• id


• date


• code> user_id


• old_favorite_movie_title


• code> new_favorite_movie_title

所以你的夜间清理脚本（或任何）运行在code> USER_FAVORITE 表寻找非标准电影标题。每次找到一个，它会更正它，并在 USER_FAVORITE_MOVIE_MERGE_COMMAND 表中记录相关事实。

您的审计跟踪就在那里，如果您需要反向清理工作，请按相反的时间顺序回放行，将新替换为 old 。

请注意，在时间的意义上，您如何具有可逆性和可审计性（例如，昨晚的批次运行 b
$ b

这是你以后的事情吗？

For example, let's say I had a social networking site for movie fans. Some people list "Rocky" as their favorite movie, others list "Rocky 1", other still "Rocky I". The obvious thing is to merge the three together and update the associated tables. However, for every obvious solution there's a design pattern that's 1) more complicated and 2) has some extra benefits. Is there a design pattern for merging duplicate database records? Specifically, something that provides auditability or reversibility?

解决方案

As soon as you as you say "reversibility" I think Command Pattern.

The typical example is to support Undo style behaviour but I think this would be a good fit for auditability as well - especially as the individual "steps" (for want of a better word) are so small and easily represented (e.g. {Merged "Rocky I" -> "Rocky" }).

How would I get the Command pattern to actually work for your scenario?

Well, keeping this very much in the RDBMS arena rather than OO modelling, assuming you've already got tables USER_FAVORITE and MOVIE, I'd add a new table USER_FAVORITE_MOVIE_MERGE_COMMAND with columns:

• id
• date
• user_id
• old_favorite_movie_title
• new_favorite_movie_title

So your nightly cleanup script (or whatever) runs over the USER_FAVORITE table looking for non-standard movie titles. Each time it finds one, it corrects it and records the pertinent facts in the USER_FAVORITE_MOVIE_MERGE_COMMAND table.

Your audit trail is right there, and if you ever need to reverse the cleanup job, "play back" the rows in reverse chronological order, replacing new with old.

Notice how you've got both reversibility and auditability both in the temporal sense (e.g. last night's batch run went weird at 2.12am, let's roll back all the work done after that) and in the per-user sense.

Is this the sort of thing you're after?

这篇关于是否有合并重复数据库记录的设计模式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！