Tax table relationship

[El_Pablo]

Hi,

I was wondering how you guys manage taxes on products in a database.

Here's a common scenario

A billing system which manage invoices and products.

Facts

• Some products are taxable others not
• Some states (provinces) can have different taxes on products. (Eg : In Quebec, we have two taxes on product)
• Taxe rates sometime changes overtime.
  

I could add more facts such as tax on tax, but I will keep the post simple although Canadian taxes are quite complicated.

Thanks for any opinion.

Edited May 29, 2009 by Guest

[stefangs]

i have created a system that can handle that using a few global fields:

tax 1 rate, tax 2 rate (in percent)

tax 1 name, tax 2 name (literals like GST, MwSt, Sales tax, etc.)

tax calc mode: tax on net/tax on tax

now when a line item is entered into the invoice, the following happens:

1) look up the tax 1 rate

2) check if there is a rate in the global tax 2 rate field.

2a) if no, then it's an invoice in a country with a single tax, so we proceed to the appropriate layout, copying the tax literal name and we're finished.

2b) if yes, we look up the rate of the tax 2 field.

3) now we need to calculate the second tax which is handled differently in some countries.

3a) if the user has selected the 'tax on net' option, then we calculate both taxes on the net amount.

3b) otherwise we calculate the tax2 based on the net plus tax1 amount.

4) proceed to the layout for dual tax invoices, look up the tax literal names and finished.

HTH,

[El_Pablo]

Thanks for your view!

Here in Canada, the taxation system seems to change every few years.

In my case in the province of Quebec

GST (tax on net) Federal tax

TVQ (tax on tax) Provincial tax

If you buy tires for your car, there's a fix tax of 3$ per tire. (fixed tax)

The GST was 7% in 2005, 6% in 2006 and now it's 5% since 2007.

So in my system I will need to keep the effective date of the taxes.

- I guess there's a "taxes" table.

- Each tax can eventually change in time.

- New taxes can be applied. (Who knows with the government...)

- Government can remove a tax (Keep dreaming)

So the relationship with the "products" table would be a many-to-many.

Is that a good approach.

[stefangs]

since in my case the tax amount is based on a lookup of the globals, it is fixed in history. old invoices must not change when the tax changes.

> In my case in the province of Quebec

> GST (tax on net) Federal tax

> TVQ (tax on tax) Provincial tax

in my system, i'd name the taxes appropriately and check the tax on tax option with TVQ being tax2.

> If you buy tires for your car, there's a fix tax of 3$ per tire. (fixed tax)

in my system, i'd call that a non-taxable amount and name it 'tire tax' on the line item. i think you could get away with that.

> The GST was 7% in 2005, 6% in 2006 and now it's 5% since 2007.

> So in my system I will need to keep the effective date of the taxes.

see above note on lookups. if the government came up with a third tax to appear on invoices, then i'd need an update. so far, i think two taxes is maximum.

i also forgot to mention that the data entry layout should have a check box for each tax, so that you can easily skip tax calculation on items which aren't taxable.

if you'd like to see my system at work, drop me a line or download studioease from my website.

[El_Pablo]

Ok, I tried to create a simple universal taxation system, but it's way too complicated for now. I attached the file here for people who might want to give a try.

taxes.zip

Edited June 5, 2009 by Guest