CODEWITHSUNDEEP
asp.netsql-server
joakim
joakim

Reputation: 1752

Delete data records - keep joined data

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.

Upvotes: 2

Views: 44

Answers (2)

shA.t
shA.t

Reputation: 16968

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.

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46231

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.

Upvotes: 3

Related Questions