如何在删除用户后将大量用户数据库记录移植到历史存储中？

I'm currently using SQL server and Entity Framework. We have a large database which consists of many additional tables linked via relationships. There are at least 15 tables that have a relationship with the main table "Users".

我目前正在使用SQL服务器和实体框架。我们有一个大型数据库，其中包含许多通过关系链接的附加表。至少有15个表与主表“Users”有关系。

eg. Users

例如。用户

UsersAddress
UsersAddress
UserProfile
用户资料
UserType
用户类型
Products
制品
Purchases
购买
... another 10 or so
......另外10个左右

Now if a user wants to be removed from the system we require to still keep transaction data etc due to taxation reasons and a host of other internal reasons. If a user deletes their account then all other tables data cascade and are lost. So we require to remove the users information but store it in another set of tables.

现在，如果用户想要从系统中删除，由于税收原因和许多其他内部原因，我们仍需要保留交易数据等。如果用户删除了他们的帐户，则所有其他表数据会级联并丢失。因此，我们需要删除用户信息，但将其存储在另一组表中。

We were thinking of creating duplicate tables with a starting table name of "Deleted". eg DeletedUsers -> DeletedUserAddress etc..

我们考虑创建一个起始表名为“Deleted”的重复表。例如DeletedUsers - > DeletedUserAddress等..

But it seems odd to pull a record by id, new up a Model, send it to the deleted table, pull the next table data, new up a Model, store it to the deleted table and so on. Then at the end delete the main user table record and all records are cascaded and removed from the db.

但是通过id，新建一个模型，将其发送到已删除的表，拉下一个表数据，新建一个模型，将其存储到已删除的表等等，这似乎很奇怪。然后在最后删除主用户表记录，并将所有记录级联并从数据库中删除。

Can anyone suggest a more efficient way of storing history data.

任何人都可以建议一种更有效的存储历史数据的方法。

I could see that if i tried

我可以看到，如果我尝试

//GET THE USER
UsersTableModel user = await db.Users.SingleOrDefault(x => x.id == 1234);

//AIM TO STORE
db.DeletedUsers.Add(user);

would not work, i would need to completely new up a DeletedUsers model and Add.

不会工作，我需要完全新的DeletedUsers模型和添加。

Is there a simpler way?

有更简单的方法吗？

Thanks

谢谢

2 个解决方案

#1

Hope it may help you!

希望它可以帮到你！

There are 15 tables have a relationship with the User table. So if you delete the user from User table then related data in 15 tables also will be deleted (cascade). So It's difficult/NoLogic to history the 15 tables data.

有15个表与User表有关系。因此，如果从User表中删除用户，则还将删除15个表中的相关数据（级联）。因此，对15个表数据进行历史记录很困难/ NoLogic。

Solution: Instead of deleting the user from the user table, Just hold the record with one column like IsDeleted in the User table. Add one new column to the user table named as IsDeleted and update it with 0(active),1(deleted).

解决方案：不要从用户表中删除用户，只需在“用户”表中按住一列，如IsDeleted。将一个新列添加到名为IsDeleted的用户表中，并使用0（活动），1（已删除）更新它。

Then while fetching User records, Transaction details you should add the filter (where clause) based on your needs. like following,

然后在获取用户记录，交易详细信息时，您应该根据需要添加过滤器（where子句）。喜欢以下，

db.Users.Where(x=>x.IsDeleted==0) // to fetch users and details except deleted users
db.Users.Where(x=>x.IsDeleted==1) // to fetch deleted users and details

#2

You can use a soft delete option where you can add one more column in your table as 'isDeleted'='0' or '1' and you can filter it based on isDeleted option ...that way you would be able to preserve your records

您可以使用软删除选项，您可以在表格中添加另一列“isDeleted”=“0”或“1”，您可以根据isDeleted选项对其进行过滤...这样您就可以保留记录

#1