如何控制引用记录的记录版本(外键)

Question

我正在开发一个应用程序，其中我有多个表，

Table Name : User_master
|Id (PK,AI) | Name | Email | Phone | Address

Table Name: User_auth
|Id (PK,AI) | user_id(FK_userMaster) | UserName | password | Active_Status

Table Name: Userbanking_details
|Id (PK,AI) | user_id(FK_userMaster) | Bank Name | account Name | IFSC

现在，我想要的是保存在记录中所做的所有更新，而不是直接更新，而应该控制版本，这意味着我想要跟踪用户之前所做的所有更新的日志。

这意味着如果用户更新地址，那么先前的地址记录历史也应该存储到表中。

我尝试过添加字段version_name、version_latest、updated_version_of字段，并在更新时插入新记录，如

|Id (PK,AI) | Name | Email | Phone | Address |version_name |version_latest| updated_version_of
    1       | ABC  |ABC@gm.com|741852|LA     |1            |0             |1
    2       | ABC  |ABC@gm.com|852741|NY     |2            |1             |1   

现在问题来了，用户表与其他两个列出的表在FK中，所以当更新记录时，它们的关系将因为新的ID而丢失。

我想保留显示为旧的旧数据，新的更新记录将只在新的事务中生效。

我如何才能做到这一点？

Answer 1

根据您的用例，您可以在表中创建一个json字段来存储以前的状态，或者为每个表创建一个新的相同的历史表。

每次用户更新任何内容时，都将整个散列转储到历史列中。或者为原始文件中的每次更新在历史表中插入新行。

Answer 2

在事务性系统中，将历史记录和当前记录存储在同一个表中不是一种好的做法。

原因是：

由于扫描更多页面以确定table

• Transactions上的record

• Additional维护工作变得更大、更长并导致超时问题，

1. 将会有更多I/O
2. 对子表进行级联引用完整性更改的额外工作

我建议将历史记录保存在单独的表中。您可以使用OUTPUT子句来捕获历史记录并将其插入到单独的表中。这样，您的引用完整性将保持不变。在历史表中，您不需要定义PK。

下面是将OUTPUT子句与UPDATE一起使用的示例。您可以阅读有关OUTPUT子句here的更多信息

DECLARE @Updated table( [ID] int,
                        [Name_old] varchar(50),
                        [Email_old] varchar(50),
                        [Phone_old] varchar(50),
                        [Address_old] varchar(50),
                        [ModifiedDate_old] datetime);
Update User_Master
Set Email= 'NewEmail@Email.com', Name = 'newName', Phone='NewPhone', Address='NewAddress'
 ModifiedDate=Getdate()
OUTPUT deleted.Id as Id, deleted.Name as Name_old, deleted.Email as email_old ,
deleted.ModifiedDate as ModifiedDate_old, deleted.Phone as phone_old, deleted.Address AS Address_old, deleted.ModifiedDate as modifiedDate_old
INTO @updated
Where [Id]=1;
INSERT INTO User_Master_History
SELECT * FROM @updated;

Answer 3

当我过去面对这种情况时，我用以下方法解决了它：

First方法

推荐的方法。

有第二个表，用作更改历史记录。因为您没有向主表添加行，所以您的外键将保持完整性。

SQL Server中现在有自动执行此操作的机制。

SQL Server 2016 Temporal Tables

SQL Server 2017 Change Data Capture

第二种方法

我不推荐这是一个好的设计，但它确实可以工作。

1. 将一条记录视为主记录，并且此记录与其他相关表中的记录保持外键关系，这些表可能发生更改，并使用任何更改更新此主记录，从而维护外键的完整性。
2. 向此表添加自引用键，例如Parent和date-of-change列。
3. 每次更新主记录时，将旧值存储到同一表中的新记录中，并将Parent值设置为主记录的id。同样，主记录永远不会更改，因此您的外键关系将与更改历史记录一起维护integrity.
4. Using date-of-change列，从而允许您在任何时间点重新构造精确值。