Re: Way to design "history" in DBs

Ruud en Maud wrote:

> Your solution looks remarkably like the model I have been working with a
> couple of years ago. I had already considered writing you about it, but I
> thought it might be to -heavy- for your requirements. Tough to say though,
> from a distance.
>
> One remark going back to this experience:
> The example query you give is a relatively simple one. Things get a bit
> more complicated when you 'd like to know the state of things at a given
> moment in time. I think in order to have this option you should also add
> something like DateIneffective. You could then query using 'WHERE
> DateEffective < :date AND :date < DateIneffective'. By the way: the
> real fun starts when you should also allow for corrections being made,
> like someone ordered some item but sent it back within a week, things
> like that.

All you have to do is prefilter all Effective Dates that are after the date you are inspecting.

Having two dates is not normal form. Maybe there are efficiency concerns, but keeping the data consistent may not be worth the gain.

Kyle Lahnakoski                                  Arcavia Software Ltd.
(416) 892-7784                                 http://www.arcavia.com