Re: nulls

On: Thu, 12 Mar 2009 18:39:27 -0400, "A.M."
<agentm(at)themactionfaction(dot)com> wrote:
>
> The one problem I foresee is that changes to the commodity_tax_rates
> table may not reflect in transaction dates that have passed. What
> happens if a tax is retroactively ended or applied outside these
> barriers? Is this tax calculation frozen at the date of the "best
> information we have"? If so, you might consider an insert-only table
> and linking the tax decision to the row that happened to be in
> effect at any time. This would likely require adding a column
> indicating when the row was inserted.

The only thing that can result in a change of rate after the point
of remittance is a re-classification of the commodity or treatment,
which would then attract the rate for the redetermined
classification in effect at the time of the original transaction.
This may occur years (up to 5 or 7 I believe) after the transaction.

All tax tables have audit columns that record the timestamp of
insertion and the timestamp of the last update and the respective
userids. The changes themselves are logged separately.

Based on the discussion here I have decided to stick with nulls for
unspecified superseded dates. There are very few places in the
system where nulls are permitted so each occurrence is exceptional
and has its own contextual meaning.

I appreciate very much the references given me to temporal SQL
writings. I found these most illuminating.

--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB(at)Harte-Lyne(dot)ca
Harte & Lyne Limited http://www.harte-lyne.ca
9 Brockley Drive vox: +1 905 561 1241
Hamilton, Ontario fax: +1 905 561 0757
Canada L8E 3C3