Re: VAT rate or VAT amount as a column?

I'm storing the VAT _rate_ per customer, per invoice. And it's editable. There's a table default of 17.5%.

As far as I can tell that handles your imagined 'problems'. Though I'm not sure what (b) is about.

I didn't dismiss Ken's suggestion 'out of hand'. If you read my original post you'll see I was asking for opinions. But in the end this seems to be the best way, at least to me. And it's also 'theoretically' correct, so I can sleep at night.

Emily

"Frank Hamersley" <terabitemightbe_at_bigpond.com> wrote in message news:W4wBg.7857$rP1.5590_at_news-server.bigpond.net.au...
> Emily Jones wrote:
>> Your suggestion destroys normalisation.
>
> True...but consider...
>
> (a) the VAT rate changes by way of Govt legislation in the future?
>
> (b) a case where the wrong amount of VAT is shown on an invoice (a sh!t
> happens event) that has found its way to a customer and can't be
> retracted?
>
> (c) a backdated invoice is issued after (a) for an event before the change
> in rate has taken effect.
>
> All of these are temporal issues that are not handled well by the state of
> the art (SQL), although theory (RM) is quite adequate.
>
> So Ken's offering of a practical solution to materialise the amount (by
> whatever means) should not be dismissed out of hand, unless a theoretical
> interest is your _only_ concern.
>
> Cheers, Frank.
>
>>
>> And who said anything about a trigger? That seems to be your idea.
>>
>> Emily
>>
>> "Kenneth Downs" <knode.wants.this_at_see.sigblock> wrote in message
>> news:c6fhq3-r32.ln1_at_pluto.downsfam.net...
>>> Emily Jones wrote:
>>>
>>>> A view isn't the same as a base table though is it? Materialised or
>>>> not.
>>> Right.
>>>
>>> My point is that if we allow system controlled derived data, and we
>>> trust
>>> the values we are given because we trust the db server, what does it
>>> matter
>>> if we put the values in a view (materialized or not), or into the base
>>> table? Further, putting them into the base table is just plain simpler
>>> and
>>> more convenient.
>>>
>>> The trigger is the mechanism that lets us accomplish a system-controlled
>>> derived value, and to put it where it is easiest to use: directly into
>>> the
>>> base table.
>>>
>>>> "Kenneth Downs" <knode.wants.this_at_see.sigblock> wrote in message
>>>> news:q0heq3-ghe.ln1_at_pluto.downsfam.net...
>>>>> Emily Jones wrote:
>>>>>
>>>>>> I thought a table with a derived column in it ISN'T normalised.
>>>>>> Functionaly dependent not on the Primary Key, or something. No?
>>>>>>
>>>>>>
>>>>> Correct. But neither is the data in many views. Both are system
>>>>> controlled
>>>>> (if you go triggers) so whatever theory allows views allows the
>>>>> materialized columns.
>>>>>
>>>>> --
>>>>> Kenneth Downs
>>>>> Secure Data Software, Inc.
>>>>> (Ken)nneth_at_(Sec)ure(Dat)a(.com)
>>> --
>>> Kenneth Downs
>>> Secure Data Software, Inc.
>>> (Ken)nneth_at_(Sec)ure(Dat)a(.com)
>>
Received on Mon Aug 07 2006 - 07:29:23 CEST