删除数据记录 - 保持联接数据

I was not able to find a better title for this.

我无法为此找到更好的头衔。

Branches          Users             Attendance
-----------       -----------       -----------
branchID^         userID^           courseID^
branchName        userName          userID*
                  branchID*

Here's my table. Due to company re-structure I need to delete old branches and the users that belong in them. But when my boss wants to see old Attendances he wants to see old userNames even if they don't exist.

这是我的桌子。由于公司重组，我需要删除旧分支和属于它们的用户。但是，当我的老板想要看到旧的出勤时，即使他们不存在，他也希望看到旧的用户名。

What's the best practice here? I'm thinking to add a Disabled column in Branches/Users so they aren't visible on the web page.

这里的最佳做法是什么？我想在分支/用户中添加一个禁用列，以便它们在网页上不可见。

2 个解决方案

#1

A "soft delete" flag is often used to address the requirement to retain both current and logically deleted data. Alternatively, you could move the rows to archive tables for historical reporting.

“软删除”标志通常用于满足保留当前和逻辑删除数据的要求。或者，您可以将行移动到归档表以进行历史报告。

Having both current and logically deleted rows in the same table is more convenient if you need combined reporting on both. The downside is the presence of the inactive rows can add more overhead for queries of active data only. Much depends on the percentage of inactive rows and the number of rows.

如果您需要在两者上进行组合报告，则在同一个表中同时包含当前和逻辑删除的行会更方便。缺点是非活动行的存在可能仅为活动数据的查询增加更多开销。很大程度上取决于非活动行的百分比和行数。

#2

I use this kind of solution:

我使用这种解决方案：

Making a Log table:

制作日志表：

[Log]
ID (bigint IDENTITY(1,1)) PK
Entity_Id (bigint) FK    --'Entity' table is list of my tables 
Row_Id (bigint)          --Is Id of the row of the `Entity`
Kind (int)               --0=Create, 1=Modify, 2=Delete, 3=Undelete
actionDate (datetime)    --'= GETDATE()'
user_Id (bigint) FK      --'User' table is list of users

Now this query gives me the state of the row:

现在这个查询给了我行的状态：

SELECT TOP(1)
    Kind,
    actionDate,
    user_Id
FROM 
    [Log]
WHERE
    Entity_Id = @Entity_Id AND
    Row_Id = @Row_Id
ORDER BY
    actionDate DESC

As result is:

结果是：

0 => Created by `user` in `actionDate`
1 => [Last] Modified by `user` in `actionDate`
2 => [Last] Deleted by `user` in `actionDate`
3 => [Last] Undeleted by `user` in `actionDate`

Note :
If you don't want to clear whole database, don't delete any row.
And when you want to delete do it in a mechanism based on relations.

注意：如果您不想清除整个数据库，请不要删除任何行。当你想删除时，在基于关系的机制中执行它。

#1