Re: VAT rate or VAT amount as a column?
From: Frank Hamersley <terabitemightbe_at_bigpond.com>
Date: Mon, 07 Aug 2006 00:47:50 GMT
Message-ID: <W4wBg.7857$rP1.5590_at_news-server.bigpond.net.au>
>> 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 - 02:47:50 CEST
Date: Mon, 07 Aug 2006 00:47:50 GMT
Message-ID: <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 - 02:47:50 CEST