470,566 Members | 1,501 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,566 developers. It's quick & easy.

Best way to delete a user

Seth Schrock
2,965 Expert 2GB
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.
Feb 10 '22 #1
2 11099
Niheel
2,446 Expert Mod 2GB
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.
4 Weeks Ago #2
zmbd
5,435 Expert Mod 4TB
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.
4 Weeks Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by =?Utf-8?B?Um9nZXIgTWFydGlu?= | last post: by
2 posts views Thread by VB Programmer | last post: by
3 posts views Thread by at_the_gonq | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.