1

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:

  • Empolyee ID - Primary Key
  • Employee Name - Name
  • Employee Surname - Surname

Taken_Treatments:

  • Treatment ID - Primary Key
  • Personnel ID - Foreign key refers to Employee ID
  • Individula - Foreign key refers to Patient ID
  • Type of treatment - Foreign key refers to Treatment ID

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:

  • set a default value (ID of a different Employee)
  • set values as null
  • cascading: deleting every row where is the employee refered to

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-

Lucian Al-Zafari
  • 37
  • 1
  • 6

2 Answers2

1

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.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • Your recommendation gets a little dicey if someone leaves, then returns. – Dan Bracuk May 28 '14 at 17:02
  • @Bracuk: true. Two possible tactics, neither ideal: (1) have a child table tracking from/through employment periods; (2) if rehired, make a new entry for the new period of employment. I'd use the first if rehires (seasonal jobs?) are likely, and the second if rehires are unlikely. Or (3) if the "downtime" need not be tracked, just reset the "Departed" date on rehire -- there should be no date-sensitive data referencing the employee during their "time off". – Philip Kelley May 28 '14 at 18:19
  • I have such column in my Taken_Treatment table called "Treatment Taken" its a "date" type column. I also I have to point out that I can recreate the database anytime, its a simple database with tables and few entries (school project). I have a long query file that delete and recreates the database tables and entries again. It is not an issue if I loose any data since they are fictional, but I would like to know if something like archiving system is possible. – Lucian Al-Zafari May 28 '14 at 18:29
1

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.

Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
  • A currently_active field would make it easier if someone leaves, then returns. If employment history is needed, it can go into another table. – Dan Bracuk May 28 '14 at 17:20
  • I generally find dates for ["soft-deletes"](http://stackoverflow.com/a/68338/880904) to be [much more useful than bits](https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/) in a database. Also, I agree that full history/archiving would add complexity (other tables), but if that is necessary, the OP really needs to state that requirement. – Tim Lehner May 28 '14 at 17:35