Hi all,

Imagine the following tables who consists of these fields..:

tblPerson (persnr, name, age, etc)
tblWork (workid, date, doneby)

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.

Thanx alot
Eelko Heuvelmans, NL

there is one very simple way to do this

since you must keep the data, therefore you cannot delete the employee

therefore, you have to use a "status" attribute for the employee

then status='no longer employed' would allow you to keep the employee data, but prevent the employee from being selected for new work (a minor change is required in your app)

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.

Ravi

Sorry for my late reply.
Thanx all for your replies i now have something to go for.

Stupid enough was that i just had posted this message and read about this kind of problem elsewhere on the forum. But that happens i guess.

Thanx all
Regards
Eelko