Here 'tblPerson' Holds al the employies of the firm i work for and tblWork holds a list of jobs/tasks that some person has done...
(In our buisines it is important to keep track of who had done what. because we are working with food, and whe have to comply to ISO-standards wich are checked every now and then)
My problem starts when a person who works for us resigns and leaves our compagny. I would like to delete him/her from the table tblPerson so this person can't be selected in different screens and apps. But when i delete him/her from the table tblPerson all the jobs/tasks he/she has done will be deleted due to the cascading effect.
This is what i want to prevent,.. because of the ISO-standards all the tasks and the name of the person who did them have to be kept in our database for later research (if needed)
Does anyone have any hints and tips on how to model these kind of things?
I'd love to hear about it.
Another option is to have a hire_date and a left_organization_date (or some such date).
Yet another option is to have an employed_until_date without the join date.
A third option is to look at the design and recognize that "employee" is a role played by a person, and hence have two different tables (enitities), one for a person as the table currently exists, and one called employee with the id, join_date and leaving_date (or effective_from, effective_until dates.) Many people do not like the third option simply because it creates an extra table. I prefer it because it seems to model the facts more closely.