[AccessD] Cascade-delete (was: Estimating Help)

Hi Arthur

> Frankly, I see the deletion of data as a symptom of a design flaw and/or a
> bad accounting practice. Suppose you have a button that cancels an order
> (deletes it and its kids). How will you ever notice a pattern if you
> actually delete the data? Suppose some enterprising employee figures out a
> way to defraud the company by placing and then canceling orders? Suppose
> there's something stupid about the UI that causes people to screw up sales
> and have to cancel them frequently? With the data gone all you have is vague
> subjectivity.

There are basically two reasons to delete data: error or obsoleteness.
Records created by error has no use and can be deleted right away.

However, with the decreasing price on disk storage you may choose to
never delete any records but instead define a valid time period for
every record. Most often the valid period starts when the record is
created (but it can be sometime in the future) and it stops when you
define it as obsolete. A record created by error can be regarded as
the special case where the valid period is set to zero - start and
stop times are equal.

For recording of the valid time period you will need two date/time
fields. If you further add date/time fields for transaction time you
end up with four date/time fields and you have a temporal database.

I have previously posted some links for info on this exciting area
(look in the archives) and here is one more:

  http://cs.nyu.edu/cs/faculty/shasha/spytime/spytime.html

Much of the material looks pretty old but the theory is valid and
alive.

The beauty of this is that you can always look at the database as it
was a moment in time between creation and now. Further, you'll by
definition record the history of the database in itself, and logging
is somehow fully automatic while being independent of the chosen
database engine.

/gustav