Something that has bothered me for awhile: whether or not to allow users to
delete what I will a "top-level" data element, for example, a name from a
list of companies. Early in an enterprise's operation of a particular
system, deleting a company name from a list would not be problematic, but
later on, much data relies on that particular name! Not only are there
plenty of relationships, but more importantly, historical data related to
that company name may exist -- that may forever be relevant. Now I
understand how to manage cascading deletes, that is not the issue. I'm
simply curious how others have handled potential deletes of that type, when,
who, and so forth.

It all depends on how critical the data are and who owns a piece of that
data. My client policy is to allow the data owners to delete their own data,
but archive the "deleted data" to other medium for 6 months, if no one
claims them, then a permanent purge is performed on the deleted records.

John

Let's say you have a list of interactions for a particular company. If you
delete the company, but not the interactions, then the interactions becomes
meaningless. You can't find the name of the company they refer to, the
company's address,etc.

One way to deal with this is to not allow deletion of entities that are
referenced elsewhere already. But, you then give the users a way to mark
them as inactive. Inactive companies should not show up on reports or
searches, unless the user specifically wants to include inactive companies.

That way the companies and all data associated with them are completely
preserved. But the typical user will never have to look at it.

"Earl" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Something that has bothered me for awhile: whether or not to allow users
> to delete what I will a "top-level" data element, for example, a name from
> a list of companies. Early in an enterprise's operation of a particular
> system, deleting a company name from a list would not be problematic, but
> later on, much data relies on that particular name! Not only are there
> plenty of relationships, but more importantly, historical data related to
> that company name may exist -- that may forever be relevant. Now I
> understand how to manage cascading deletes, that is not the issue. I'm
> simply curious how others have handled potential deletes of that type,
> when, who, and so forth.
>

Ahhhh, very nice Marina! Thanks to both you and John.

"Marina" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Let's say you have a list of interactions for a particular company. If
> you delete the company, but not the interactions, then the interactions
> becomes meaningless. You can't find the name of the company they refer to,
> the company's address,etc.
>
> One way to deal with this is to not allow deletion of entities that are
> referenced elsewhere already. But, you then give the users a way to mark
> them as inactive. Inactive companies should not show up on reports or
> searches, unless the user specifically wants to include inactive
> companies.
>
> That way the companies and all data associated with them are completely
> preserved. But the typical user will never have to look at it.
>
> "Earl" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Something that has bothered me for awhile: whether or not to allow users
>> to delete what I will a "top-level" data element, for example, a name
>> from a list of companies. Early in an enterprise's operation of a
>> particular system, deleting a company name from a list would not be
>> problematic, but later on, much data relies on that particular name! Not
>> only are there plenty of relationships, but more importantly, historical
>> data related to that company name may exist -- that may forever be
>> relevant. Now I understand how to manage cascading deletes, that is not
>> the issue. I'm simply curious how others have handled potential deletes
>> of that type, when, who, and so forth.
>>
>
>

I tend to be very careful with deleting "top-level" data elements, and I
NEVER use cascading deletes (with VERY few exceptions). I consider this a
"best practice". It forces an app developer (me or whoever follows) to
consider the ramifications and to explicitly delete subordinate data. Also,
if other relationships are added (following that best practice) after the
application was written, the application must be updated to delete these
related records - which again means the developer has to consider if there
are any ramifications to doing so.

If historical data (whether related to the deleted company directly or
indirectly) is important, I would not delete the company. I would instead
mark it as "inactive" with a Status column. If I wanted to make things
simple, I might also possibly create views to show only the "active"
companies and their related data.

Depending on the amount of data in your database, and the types of usage
required for the "historical" data, I may move the data to one or more
"history" tables.

One example where it would be critical to keep the "historical" data (but
not as "history of the deleted company"), would be where the "deleted"
company may have been a client that our enterprise had some sort of
transactions against. Even if we do not need to be concerned with the
"deleted company" per se, we would probably want to be able to show our
enterprise's transactions.

Regards,
Tore.


"Earl" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Something that has bothered me for awhile: whether or not to allow users
> to delete what I will a "top-level" data element, for example, a name from
> a list of companies. Early in an enterprise's operation of a particular
> system, deleting a company name from a list would not be problematic, but
> later on, much data relies on that particular name! Not only are there
> plenty of relationships, but more importantly, historical data related to
> that company name may exist -- that may forever be relevant. Now I
> understand how to manage cascading deletes, that is not the issue. I'm
> simply curious how others have handled potential deletes of that type,
> when, who, and so forth.
>