DB Design Question please

• Quoting David_at_Inform.Co.Nz to All dated 02-28-94 ***
  > 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 won't bore you with a complete discussion of cascading granularity, but my suggestion would be to do both.

What?

Absolutely -- the tax table should be fully normalized, as you have suggested, and when transactions are entered or modified, the tax table is consulted to create an entry in the denormalized field using a database trigger. You have the option of disallowing the update of the denormalized field (most likely, in this case) or allowing it to "override" the tax tables.

Also note: another trigger would generally be used to update the transaction table when the tax tables are changed. If not, you should prevent the tax tables from being changed when transactions exist within the range of the tax tables using a trigger.

Michael Stowe
Director of Technical Services
Constellation Engineering Received on Thu Mar 03 1994 - 08:26:01 CET