CODEWITHSUNDEEP
sql
Lucian Al-Zafari
Lucian Al-Zafari

Reputation: 37

SQL - Deleting table entries and archiving?

Ok, here is the breakdown of my problem. I have two table:

Employees and Taken_Treatments. Here is the list of columns for each table:

Employee Table:

Taken_Treatments:

I have 5 employees and 7 treatments.

Now here is the problems: I fired an employee thus I have to delete his entry from the employee table. I have dealt with the foreign key contraint and thats where the problem begins: I have three choices:

For the employee table, it is not a good practice to have hired and fired employee in the same table. Moreover, on Taken_treatments table, I cannot have null values for an employee and I dont want neither update the table with another employee ID nor deleting a Taken_Treatment entry with the fired doctor.

So, I want to do this: Instead of deleting the employee permanently, I would like to put him to another table (lets say Former employees) so Taken_Treatment entries stays intact (something like archiving), is something like that possible? -Thank you-

Upvotes: 1

Views: 129

Answers (2)

Tim Lehner
Tim Lehner

Reputation: 15251

Because you want Taken_Treatment entries to stay intact (meaning they still point to the fired EmployeeID until you update them), you cannot delete the fired employee from the table. A foreign key cannot refer to more than one column, such as you seem to be asking for with your Former_Employees table.

I would suggest a simple TerminationDate column that is normally null for active employees, or a Terminated_Employees table that references EmployeeID if you can't use nulls.

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40359

Sorry, no clear-cut answer from me here. You are on the tip of a fairly sizeable iceberg -- a classic temporal database problem, compounded by possible fourth- and fifth-normal form issues. This kind of problem must be addressed during the database design phase.

How to design the tables depends on the overall purpose and goals of the database. Leading questions:

  • How long does is the data to persist? When can an employee be permanently removed from the system? When can Taken_Treatments be removed?
  • Assuming Taken_Treatments must be persisted for a very long time (HIPAA, insurance, IRS, etc. etc. etc.), then you probably have to track the Employees who performed the treatment as well. Is one table sufficient? Perhaps one table for “current treatments”… which may mean treatments handled by current employees, and one for "all treatments", which does not reference which employee performed the treatment?
  • Having a single table each for employees and treatments is probably more useful (and certainly simpler to work with) than having one for “current” treatments and employees and one for “all historical” treatments and employees.

The “fix” depends on the desires/requirments, and as you can see it gets complex pretty quickly. Assuming you need to keep data for a prolonged period of time, and assuming turnover, I would recommend adding datetime columns like “Started” and “Departed” in the Employees table, allowing you to determine when they were “active” employees (if Departed < now, they are an ex-employee). You did not indicate a “time of treatment” column in Taken_Treatment, is that a relevant attribute to your business? ...and on and on the design questions go.

Upvotes: 1

Related Questions