Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Location
    Arnhem, Gld, NL
    Posts
    21

    Arrow How to keep linked values in DB when deleting PK's

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Some more options

    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

  4. #4
    Join Date
    Dec 2005
    Location
    Arnhem, Gld, NL
    Posts
    21
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •