To save or not to save "Sales Tax"

[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?

 

[Duane Hookom]

Sales tax will change over time (like prices). I would probably save the
sales tax rate in the sales detail or header records.

 

[Russ via AccessMonster.com]

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

 

[Duane Hookom]

I would not store both the rate and the amount.

 

[Allen Browne]

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.

 

[Russ via AccessMonster.com]

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?

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.

[quoted text clipped - 15 lines]

 

[Allen Browne]

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.

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?

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.

[quoted text clipped - 15 lines]

my
orders table or just calculating it? What are pros and cons?

 

[Tom Lake]

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?

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

 

[Russ via AccessMonster.com]

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

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,

[quoted text clipped - 26 lines]

 

[Allen Browne]

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.