To save or not to save "Sales Tax"

  • Thread starter Thread starter Russ via AccessMonster.com
  • Start date Start date
R

Russ via AccessMonster.com

What is general opinion on this, should I be saving sales tax data in my
orders table or just calculating it? What are pros and cons?
 
Sales tax will change over time (like prices). I would probably save the
sales tax rate in the sales detail or header records.
 
Hello Duane,

I am currently saving the tax rate in the order table, but should I save the
total amount charged for the sales tax of just calculate it?

example
(should I save this)
sales tax rate total sale Sales tax
6.5 % $10.00 .65
 
Russ, one of the basic rules of data normalization is not to store dependent
data. The tax *rate* is independent of the value of the order, but the tax
amount is not. Therefore, storing the rate as Duane suggested is the correct
approach.

You also asked whether the rate should be stored in the order header table
or in the detail table. In some places, there are items that are tax exempt.
In my country, for example, unprocessed food is tax exempt, where as
pre-processed food is taxable. If you thought your database might ever have
to cope with that situation, you would need to store the tax rate in the
detail table. If you are sure that situation will never arise, you could
choose to store the tax rate in the header table.
 
Allen,

Would you store the tax rate and taxable in the product table?
example
partnumber1 0.5% (tax rate) yes (taxable?)
partnumber2 0.0% (tax rate) No (taxable?)
Is this the correct way of doing it?

Allen said:
Russ, one of the basic rules of data normalization is not to store dependent
data. The tax *rate* is independent of the value of the order, but the tax
amount is not. Therefore, storing the rate as Duane suggested is the correct
approach.

You also asked whether the rate should be stored in the order header table
or in the detail table. In some places, there are items that are tax exempt.
In my country, for example, unprocessed food is tax exempt, where as
pre-processed food is taxable. If you thought your database might ever have
to cope with that situation, you would need to store the tax rate in the
detail table. If you are sure that situation will never arise, you could
choose to store the tax rate in the header table.
I would not store both the rate and the amount.
Click to expand...
[quoted text clipped - 15 lines]
Click to expand...
 
Depends.

You would not store both the tax rate (number, double) and IsTaxable
(yes/no). A tax rate of zero defines not taxable.

In most cases I think you would put products in a category, and specify the
tax rate for the category. If the govt did decide to change the tax rate,
changing every individual product could be a pain, and products of one type
should (by definition) be able to share the same tax rate.

Of course, the price and tax rate you store in the Product table is the
current price and tax rate, so you still need them in your OrderDetail (i.e.
the actual price and tax rate at the time of the sale.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Russ via AccessMonster.com said:
Allen,

Would you store the tax rate and taxable in the product table?
example
partnumber1 0.5% (tax rate) yes (taxable?)
partnumber2 0.0% (tax rate) No (taxable?)
Is this the correct way of doing it?

Allen said:
Russ, one of the basic rules of data normalization is not to store
dependent
data. The tax *rate* is independent of the value of the order, but the tax
amount is not. Therefore, storing the rate as Duane suggested is the
correct
approach.

You also asked whether the rate should be stored in the order header table
or in the detail table. In some places, there are items that are tax
exempt.
In my country, for example, unprocessed food is tax exempt, where as
pre-processed food is taxable. If you thought your database might ever
have
to cope with that situation, you would need to store the tax rate in the
detail table. If you are sure that situation will never arise, you could
choose to store the tax rate in the header table.
I would not store both the rate and the amount.
Click to expand...
[quoted text clipped - 15 lines]
my
orders table or just calculating it? What are pros and cons?
Click to expand...
Click to expand...
Click to expand...
 
Russ via AccessMonster.com said:
Allen,

Would you store the tax rate and taxable in the product table?
example
partnumber1 0.5% (tax rate) yes (taxable?)
partnumber2 0.0% (tax rate) No (taxable?)
Is this the correct way of doing it?
Click to expand...

If you need a historical record, then yes, store the tax rate rather than
doing a lookup each time. If the tax rate should change, then all the
records originally using the old rate would be incorrect if you were
to look up the value on the fly.

Tom Lake
 
Ok, thanks for the help / advise.
Not sure what you are saying here, can you provide example?
In most cases I think you would put products in a category, and specify the
tax rate for the category. Thanks..
Click to expand...

Allen said:
Depends.

You would not store both the tax rate (number, double) and IsTaxable
(yes/no). A tax rate of zero defines not taxable.

In most cases I think you would put products in a category, and specify the
tax rate for the category. If the govt did decide to change the tax rate,
changing every individual product could be a pain, and products of one type
should (by definition) be able to share the same tax rate.

Of course, the price and tax rate you store in the Product table is the
current price and tax rate, so you still need them in your OrderDetail (i.e.
the actual price and tax rate at the time of the sale.)
Allen,
Click to expand...
[quoted text clipped - 26 lines]
Click to expand...
 
In the Northwind sample database, I'm suggesting that if you want to store
the current default tax rate, it would be better to put that field in the
Categories table rather than the Products table.
 
Back
Top