Orphaned childs due to lookup table deletes

[1DMF]

Hi,

I was wondering how you guys deal with lookup table FK's where say one table is 'Staff' , someone leaves, if you delete a member of staff (no cascade), all child records are now orphans to the lookup table.

But if you leave the x-staff in the table it now appears in the front end application as an option in the drop down which obviously we don't want.

woul you add an extra column in the lookup table say 'Active' as boolean or int 1 = true / 0 = false or string 'yes/no' , however, and then amend the front end to exclude non-active records.

Thus keeping the correct parent child link for records that still need to have the x-staff name against them.

Or do you delete from the lookup and just allow orphans?

What is 'best practice', what do you do?

Thanks,
1DMF

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download

 

[SQLBill]

I would have an 'active' value, either a bit like you mentioned or a date (maybe two columns - start_date and end_date).

We need to be able to show historical data for legal reasons. For example....Staff John Smith leaves and he supervised Jane Doe and Sally Jones. After John left, his record was deleted, but now that he's gone and Jane and Sally fell there won't be any repercussions from him...they file a harrassment charge against him. How are you going to recover his information?

Or a year from now, you need to find out who Mark Smith's supervisor was?

Your child records may not be supervisee's, but other information....how might it be needed in the future?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[1DMF]

If you have an FK constraint, but do not cascade deletes, it will leave the orphaned child records, and as the PK/FK is usually varchar (in this case anyhow),and not enumeration, the field in the child orphan can still be searched and querried.

This is a good argument for not enumerating lookup tables, also not requiring loads of joins for querries and reports to enumerate lookup data for the description to be user friendly, if enumeration is mainly used.

But is that good practice? or just another way to skin the cat?

I think I will add (to those that don't) an 'active' column tinyint, default 1, or set to 0 , then when people moan that someone they don't want is appearing in the front end application dropdown lists, I'll use the additonal field to eclude unwanted records in drop down.

What is the definitive argument for or against enumerating lookup tables?





"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Free Electronic Dance Music Download