Re: DB Design Question please

In article <CLyx2B.CG5_at_inform.co.nz> david_at_inform.co.nz "David Paulo" writes:
> The problem that I am trying to solve is fairly generic and is illustrated
> by the following case.
>
> Tax rates change from time to time and transactions (financial, not database)
> posted at a particular time and for a particular tax rate will have that
> tax rate applied to them when being billed, credit notes issued, tax returns
> filled in and so forth. When the tax rate changes, there may be a short period
> when transactions are posted that must have the old rate applied because
> the transaction was actually completed prior to the date of change of the tax
> rate.
>
> My question is how best to design the application to handle this.
>
> One option is to have a table called tax that is created as follows:
> create table tax
> (tax_type char(1),
> change_date date,
> tax_rate number(4,3)
> )
>
> and the transaction table then has an attribute called tax_type and
> transaction_date. Each time the tax rate is required the tax table is
> accessed using the tax_type and transaction_date to find the tax_rate row
> whose change_date is older than the transaction_date.
>
> Another option is to have the tax_rate as an attribute of the transaction table
> and to copy the tax rate into the row when it is created.
>
> As I understand the rules of normalisation the first option is more desirable
> but it seems very clumsy to me as every report, every screen that has to show
> the tax rate or the taxed amount has to access the tax table with what
> must be a rather convoluted peice of sql.
>
> I realise that this is probably a trivial question.
> Thank you for your patience.
>
> ----------------------------------------------------------------------
> David Paulo, InForm Group Ltd.,PO Box 1444, Wellington, New Zealand.
> Ph: +64 4 472 0996 Fax: +64 4 473 2407 Email: david_at_inform.co.nz
> ----------------------------------------------------------------------
>
>

How about having attributes like tax_effective_date and tax_end_date in the tax table. Then when transactions are entered you can easily what tax rate to use e.g.

select tax_rate
from tax_table
where sysdate between tax_effective_date and tax_end_date;

When new tax rates are entered, the end date of the previous one is set to the start date of the new one (or maybe start date - 1 or something). The current tax_rate could have a 'high' date e.g. 31-DEC-2999.

Just a thought...

-- 
Larry Barea