LINQ / SQL question.

[Cogman]

Ok, so I am working with this database and have come to a crossroad which I would like some advice on.

This Database deals with employee charged expenses to the company and timesheets. So here is my quandary.

What if an Admin wants to delete an employee? Since the employeeID is linked to all the timesheets and expenses that they have done, the only way to delete an employee is to delete all that data associated with that employee. As you could imagine, that could be a bad thing if the company ever wants to look at former transactions/timesheet entries.

So here is what I'm thinking. Don't delete the employee, just disable them (IE flip a bit in one of the columns to say "This employee is no longer active" and hide the employee from management panels. Is this a good idea? Should I not even provide the ability to delete an employee? Or should I give them the option with a strict warning "Hey stupid, this can really mess things up."?


The second part, is there an easy way with linq to find and delete all foreign key references to a database element? (without going to each table and explicitly saying "Delete all elements that have this key associated with it")

 

[nickbits]

i would just make a disabled/deleted flag.

here is some info otherwise:
http://www.codeproject.com/KB/cs/CascadingDeletes_cs.aspx

 

[Kr@n]

+1 for flagging. I sometimes implement a kind of trash (flag first, and then allow the admin/super-admin to delete / clean the trash). This is also useful when implemented for important/precious data, or like companies (clients), bills, etc. : you can then keep trace of the "delete" command within dedicated fields directly on the item (DELETED_DATE, DELETED_BY, etc.) and undo the deletion if needed.


As for LINQ, I first thought it was possible to define the cascading policies when linking your tables in DBML, and that LINQ would do everything for you, but it seems it's not.

 

[jvroig]

So here is what I'm thinking. Don't delete the employee, just disable them (IE flip a bit in one of the columns to say "This employee is no longer active" and hide the employee from management panels. Is this a good idea? Should I not even provide the ability to delete an employee? Or should I give them the option with a strict warning "Hey stupid, this can really mess things up."?

Provide them with a "delete" functionality, because they will look for one.

Behind the scenes, all it does, however, is "mark as inactive/disabled", and thus invisible in most data grids / list views / etc. However, reports that are historical or analytical in nature and comprised of date ranges SHOULD most likely include such "deleted" employees - for example, the company wants to see a graph (months as data points) of the total amount of expenses charged to the company within the last 3 years. If you do not include the "deleted" employees, your system will output wrong data (it will not match with what accounting can verify).

Also, make a "purge" functionality that actually allows you (the system admin or developer or whoever else who should know what he is doing) to really delete something from the database - for example, records that were "deleted" by a user due to some trivial reason (wrong spelling or info then decided to start a new record from scratch, whatever) and this record has no transactions associated with it yet. This way, you can delete for real those records that really should be deleted - the difference being YOU know what you are doing, so it's ok for you to have the power of delete, while the rest will only have something to placate them and clean up the data grids / list views that they see so that they don't bother you all the time to "delete" something, while making all reports output correct data all the time.

 

[Markbnj]

Get the semantics right. What are the reasons why an admin would delete an employee? The scenario you're describing here is usually handled through fields that capture an employee's start date and end date. If an end date is null then the person is a current employee. Most organizations never delete employee records. There are other approaches to the same requirement, but the key point is: don't put the feature there just because you think they'll need it, and don't call it something other than what it is.

 

[jvroig]

The scenario you're describing here is usually handled through fields that capture an employee's start date and end date.

Another valid approach. It's still different from a delete though, the beauty of handling user "deletes" as flags for active/inactive is that it is a technique that can be used in most other scenarios outside of "employees".

For the record, our employee database is designed as you have described - as long as the resignation_date field is empty, employees are active, otherwise they are then treated as inactive.

don't put the feature there just because you think they'll need it, and don't call it something other than what it is.

I would have to disagree here.

From a recent thread he made, I recognize that:
1) Cogman is an undergrad, but close to graduating and getting a "real job" as he called it.
2) His experience, in his own words, is limited to small outfits.

Therefore, we can reasonably assume that this particular project of his is no different, and we can assume the ff:
1) This is a small outfit who commissioned him
2) He (Cogman) is probably the only "IT expert / programmer" they have (if they had any, why would they contract out to a freelancer, especially an undergrad?)
3) The only people Cogman talks to there are plain and simple users. No project architects, no project managers, no technical specifications core group, no 200-page technical specs, nothing.

Most likely, what happened was Small Outfit Corp asked Cogman "Hey, we need this small database app just to summarize expenses charged to us and stuff. What do you think, can you do it?" to which Cogman replied "Yeah, cool".

With something like that being the case, then what Cogman is expected to do is exactly to think of what the users may need (not just this "delete" problem he encountered). He is, by all accounts, acting not just a developer, but as a freelance consultant. So beyond this "delete" conundrum he had, it is definitely his job as their freelance consultant to foresee their needs regarding this project, implement features they might need or look for, even if the users never mentioned it at first (users don't know what they want, and they expect things that they think should be "obvious" even if they never mentioned it and it isn't obvious at all to lay persons outside of their domain). He also needs to recognize that some of the things that users want are no good or are harmful, and therefore it is his job to implement them in such a way as to mitigate the harm that may be caused.

If he were in a corporation, with 20 other IT guys, 3 project managers, and one architect per project, he could certainly go about it the way you advise. He'll need to do it by the book, as the technical specs call for. But he isn't, so he shouldn't.

 

[Markbnj]

Ok, I'll buy that as a possible scenario, and I think I went overboard in any case. Sometimes you do need to put a feature in because you think it will be needed. But I stick by the "call it what it is" point .

 

[GeekDrew]

For what it's worth, I disagree with "call it what it is". A *lot* of people want to "delete" things, because they don't want to see it any more. I've seen a *lot* of products that use flags for deleted, and have a purge function for when records truly are to be deleted from the database. The users don't realize that they aren't truly deleting something, and the folks that understand data integrity (and have the rights to purge) know enough not to purge unless they've made sure the data is in a state consistent for deletion.

 

[Markbnj]

For what it's worth, I disagree with "call it what it is". A *lot* of people want to "delete" things, because they don't want to see it any more. I've seen a *lot* of products that use flags for deleted, and have a purge function for when records truly are to be deleted from the database. The users don't realize that they aren't truly deleting something, and the folks that understand data integrity (and have the rights to purge) know enough not to purge unless they've made sure the data is in a state consistent for deletion.

Ok, well we're just overgeneralizing at this point. The reason I objected to calling it a delete is that employees are not usually deleted. In the context of Cogman's original post if the semantics of the problem were understood then it is likely that magic (or soft) deletes would not be necessary.

 

[Train]

I would look into possible security ramifications. Maybe H.R. really wants to remove the record of an employee?

In addition to just flagging the person as disabled, rename them to "FORMER EMPLOYEE 001" and reset birthdate, SSN, phone, address, etc, to empty/default/whatever. That way you can keep the work records of who did what, and not mess up the totals per project, but without the added risk of a bunch of old personal info lying around.

 

[GeekDrew]

I would look into possible security ramifications. Maybe H.R. really wants to remove the record of an employee?

Instead of just flagging the person as disabled. Rename them to "FORMER EMPLOYEE 001" and reset birthdate, SSN, phone, address, etc, to empty, address of the company, or whatever. That way you can keep the work records of who did what, but without the added risk of a bunch of old personal info lying around.

I also strongly disagree with that. Employers are required to keep payroll data for a period of time - sometimes very lengthy. That includes SSN, particularly for IRS records. Depending on the nature of the industry and regulations imposed thereupon, all employee records could be required to be kept permanently. If the employer needs to change their data in that manner, why couldn't they manually do it?

 

[Train]

I also strongly disagree with that. Employers are required to keep payroll data for a period of time - sometimes very lengthy. That includes SSN, particularly for IRS records. Depending on the nature of the industry and regulations imposed thereupon, all employee records could be required to be kept permanently. If the employer needs to change their data in that manner, why couldn't they manually do it?

I wasnt speaking of a payroll system, usually those are separate (in our case, off site), but ya, records have to be maintained for a certain number of years.

 

[GeekDrew]

I wasnt speaking of a payroll system, usually those are separate (in our case, off site), but ya, records have to be maintained for a certain number of years.

Since OP mentioned expenses and timesheets, I assumed we were discussing payroll... I didn't realize you were talking about employee records outside of payroll. Sorry.