Re: VAT rate or VAT amount as a column?

Emily Jones wrote:
> 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.

Don't worry - you can always build it in to version 2!

> 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.

Sweet dreams. BTW lack of appreciation in (b) suggests to me you have not yet worked in industry there a student question QED.

Cheers, Frank.
>
> 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)