Best way to delete a user

[Seth Schrock]

Most of my databases have something to do with logging who is doing what among other things. As such, I have a table for employees with all their information and link that information to the audit record using the primary key. My issue then is what is the best way to "delete" a user when they no longer work for the company? If I actually delete the employee record, then I end up with orphaned records in the audit table.

I'm not looking for exact code or some special procedure. I'm just needing an idea for how everyone else handles this. I've used a couple of methods personally, and none get me real excited.

Things I've tried:
1) Create a "Deleted User" employee and when I want to delete a user, all their audit records get assigned to the "Deleted User" before actually deleting the employee.

2) Have an "Active" flag on the employee record so that when they quit, the flag gets unchecked and thereby removed from all available employee lists.

3) I haven't done this one, but thought about it. Instead of using the employee's primary key to create a link in the audit table, I could use the employee's name. Then when the employee's record is deleted, the name is still sitting in the audit table and the orphaned record doesn't matter.

4) Do nothing and let the list of employees keep growing.

Again, I'm not looking for specific code. Just wanting some general ideas for how to handle it.

[Niheel]

Seth,

Wouldn't the first option create a bit of a problem if there are multiple "Deleted User" employees and the audit records get jumbled.

The second option is seems like a better approach, but that would probably require code/query modifications to not show employees with that flag?

I like the flag approach.

[zmbd]

I use an active flag and occasionally another related table for terms of employment ...

At my work we have an outside panel from a temporary agency, these people come and go; however, with sarbanes oxley act, we have to keep certain types of records for quite a long time but I don't want the inactive temporaries to show up in the dropdowns etc... so I use the flag to indicate status (not exactly normalized as you'll see); however, I may need them for aggregate reports for the times that they were active on panel hence the second table {PK_TmpPnlMemActv}{FK_TmpPnlMem}{StartActiveDate}{ EndActiveDate} - In a perfect world, I would use only this second table, pull up all record where {EndActiveDatae} Is Null pull the innerjoin between the two tables, return matching records ... I got lazy, over a dozen dropdowns, I just filter on the active flag.