如何支持恢复rdb中的复杂依赖关系 [英] How to support restoring complex dependencies in a rdb

问题描述

我有一些麻烦为这个预见的情况制定了一个解决方案。

I am having some trouble formulating a solution for this foreseen situation.

TableA有一个FK到TableB。 TableC有一个FK到TableA。 TableC有一个FK到TableD。

TableA has a FK to TableB. TableC has a FK to TableA. TableC has a FK to TableD.

TableA
------
taId
tbId

TableB
------
tbId

TableC
------
tcId
taId
tdId

TableD
------
tdId

一系列简短的事件：

1）将级联软删除发给记录表B。这将导致表A中的所有相关记录被软删除，级联到TableC中的所有相关记录都被软删除。

2）稍后，TableD中的记录被软删除，再次级联。 TableD中的这个记录作为FK在TableC中进行。但是，TableC已被标记为软删除。

3）请求恢复步骤1中的TableB中的记录。

1) A cascade soft-delete is issued to a record in TableB. This causes all related records in TableA to be soft-deleted, cascading to all related records in TableC being soft-deleted.

2) At a later time, a record in TableD is soft-deleted, cascading again. This record in TableD was held as a FK in TableC. However, TableC was already marked as soft-deleted.

3) The record in TableB in step 1 is requested to be restored.

如何将相关记录恢复到TableB中的恢复记录（即TableA和TableC）中，我不会恢复任何依赖于此外，从第2步，TableD的记录，在不恢复依赖于表D记录的部分时，我以级联方式限制恢复（即如果发现TableC与软删除记录）？

How can I make sure that when restoring the related records to the restored record in TableB (namely in TableA and TableC) that I do not restore any which had a dependency on the record from Step 2, TableD - moreover, that in not restoring parts which had a dependency on the record from Table D, I restrict restoration in a cascading manner (i.e. that TableA not be restored if TableC is found to have a relation to a soft-deleted record)?

我已经考虑的是利用表中的时间设计以及GUID。每个表将具有（除了软删除标志）DateDeleted字段和GUID字段（其中相同的GUID将被分配给级联软删除中的每个节点以区分级联集合）。这将使它很容易恢复级联集合，因为它们将共享日期和GUID。但是我遇到的问题是上述问题，是如何处理软删除记录从不同级联中删除的情况。

What I have considered is leveraging a temporal design in the tables along with a GUID. Each table would have (aside from a soft-delete flag) a DateDeleted field, and a GUID field (where the same GUID would be assigned to each node in a cascade soft-delete to distinguish the set of cascade). This would make it pretty easy to restore a cascaded set because they would share a date and a GUID. But the issue I came across, which is the one outlined above, was how to handle the situation where the soft-deleted records would have been removed from a different cascade.

推荐答案

由于您的 TableD 记录是软删除的，您不必担心通过恢复您的第一个软级联删除来破坏参照完整性。但是，级联还原逻辑需要做的是测试其中进行软恢复的每个表的其他依赖关系。

Since your TableD record is soft-deleted, you don't have to worry about breaking referential integrity by restoring your first soft cascade delete. However, what your cascade restore logic needs to do is to test the other dependencies of each table in which a soft-restore is being made.

如果存在对本身已被软删除的父项目的引用，那么您还需要对该父项目进行软恢复。

If there is a reference to a parent item which itself has been soft-deleted, then you need to do a soft-restore of that parent item too.

实现这一点的一个好方法是让每个表的软删除存储proc（或者触发器）和每个表的软恢复存储proc（或触发器）。这些处理将把您的日期/ GUID（软删除业务事务ID）作为输入参数。

A good way to achieve this is to have a per-table soft delete stored proc (or trigger) and a per-table soft restore stored proc (or trigger). These procs will take your date/GUID (soft delete business transaction ID) as input parameters.

对于每个表，有问题的proc将知道依赖关系。软删除具有下游级联依赖关系，软恢复将具有上游级联依赖关系。每个进程都会将其自己的表的记录标记为已删除或恢复，然后调用执行级联操作所需的过程。

For each table, the procs in question will know what the dependencies are. The soft delete has downstream cascade dependencies and the soft-restore will have upstream cascade dependencies. Each proc will mark its own table's record as deleted or restored and then call the procs necessary to perform the cascade actions.

这篇关于如何支持恢复rdb中的复杂依赖关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！