Storing Tax Rate in database

[Ruegen]

I'm not sure how I should store a tax amount

Currently we handle a GST tax in Australia of 10% applied to all sales

I have noticed in databases some store it as 0.1 where as I think of it as 10.00 since our general math is

a = ((b /100) * 10) + b

however with 0.1 I would have to

a = (b * 0.1) + b

I would store either as a double but which way is proper? (programming and in a database table) and why?

[CJ_London]

It is entirely up to you whether you store 0.1 or 10, your subsequent calculations would handle it appropriately. I would store it as a double either way - what is to stop the rate changing to 7.5% at some point in the future? Personally I would store as 0.1 because a) it can be easily formatted as a percentage and b) the calculation is simpler so less prone to error.

However what you should also include in your table is an effective from date. This is because if the government changes the rate, you will need to know what rate was in effect for any given day.

The other thing I believe is relevant to the Australian tax is that the rule is to always round down. Your calculation does not do that. i.e. if an item cost $7.65 the tax should be $0.76, whereas your calculation will produce $0.77 if rounded to 2 decimal places

i.e. round(7.65*0.1,2)=0.77

so your calc needs to be modified slightly to

round((7.65*0.1)-0.001,2)=0.76

in your parlance

a=round((b*0.1)-0.001,2)+b

[ranman256]

I have a table called tConfig w a field taxRate as .06
I use this table to pull in the rate

[Ruegen]

It is entirely up to you whether you store 0.1 or 10, your subsequent calculations would handle it appropriately. I would store it as a double either way - what is to stop the rate changing to 7.5% at some point in the future? Personally I would store as 0.1 because a) it can be easily formatted as a percentage and b) the calculation is simpler so less prone to error.

However what you should also include in your table is an effective from date. This is because if the government changes the rate, you will need to know what rate was in effect for any given day.

The other thing I believe is relevant to the Australian tax is that the rule is to always round down. Your calculation does not do that. i.e. if an item cost $7.65 the tax should be $0.76, whereas your calculation will produce $0.77 if rounded to 2 decimal places

i.e. round(7.65*0.1,2)=0.77

so your calc needs to be modified slightly to

round((7.65*0.1)-0.001,2)=0.76

in your parlance

a=round((b*0.1)-0.001,2)+b

I have everything rounded to 2 so

a = round(f,2)

however that is currently storing the persent as 10.00 not .1

it calculates everything correctly giving me the correct amount to the nearest 5 cent when calculating the price - gst or viewing just the gst

-0.001,2

is that because you are storing it as .0 value

[CJ_London]

is that because you are storing it as .0 value

yes - actually just realised the typo, it should be 0.005 since a sale of $7.69 should also have tax of 0.76. If you are using 10 then the value would be 0.5.

Don't take my word for it re the tax always being rounded down - I'm no expert - check with your accountant. I knew because I responded a while ago to another OP from your part of the world who could not work out how to round down their tax - and told me it was a requirement in Australia.

VBA rounding (Access and Excel) uses what is called bankers rounding to even out bias - google 'bankers rounding' to find out more

[Ruegen]

yes - actually just realised the typo, it should be 0.005 since a sale of $7.69 should also have tax of 0.76. If you are using 10 then the value would be 0.5.

Don't take my word for it re the tax always being rounded down - I'm no expert - check with your accountant. I knew because I responded a while ago to another OP from your part of the world who could not work out how to round down their tax - and told me it was a requirement in Australia.

VBA rounding (Access and Excel) uses what is called bankers rounding to even out bias - google 'bankers rounding' to find out more

That's just it - I can't find a consistent way that people store the data for the tax rate if they do store it at all.

I want to store 5 fields

Date
Tax Rate
Tax Division <-- how we work backward in our tax system is we divide the price in tax then divide it by 11
Price Ex Tax
Price Tax
GST INC <-- this would be a bool to let me know if it did or didn't have tax (for database purposes)

If I store the date I will know when the date was that the tax was at that time but since I have a created_at date field for the row anyway I won't need to add that field.

that way when I query I can use a function that works out the tax by date because if the tax rate changes the calculation for the record at that time won't.

[CJ_London]

That's just it - I can't find a consistent way that people store the data for the tax rate if they do store it at all.

does it matter what others do or don't do? It's what you want to do. I'm an accountant and I always store values as they are - 10% is 0.1 so I store 0.1

Tax Division <-- how we work backward in our tax system is we divide the price in tax then divide it by 11

Should not need to store this - it is easily calculated from the rate -priceinTax/(1+taxrate) assuming 0.1 rate not 10 rate and the 11 will change if the tax rate changes.

GST INC <-- this would be a bool to let me know if it did or didn't have tax (for database purposes)

Also does not need to be stored, it is a simple calculation GST=PriceTax<>PriceExTax

If I store the date I will know when the date was that the tax was at that time but since I have a created_at date field for the row anyway I won't need to add that field.

I'd assumed you are creating the value and you were wondering about storing the tax rate in a table to reference when you wanted to calculate the tax on a sale. The normal policy for invoices is to store the net, gross and tax plus tax rate in the invoice so it cannot change.

But back to the tax table and why you need an effective date - say the taxman says that from 1st May 2015 the new rate is 12%. If you have a simple lookup in a table for the tax rate (or worse it is hard coded into your calculation) on the 1st May, first thing in the morning, not before, not after, you change the rate from 10 to 12 and off you go again. But on the 10th May, you realise you haven't completed some invoices for April - what you going to do, change the calculation back? run the invoices and then change it again?

[ranman256]

The storing of the rate in a table is for future change. If you code it, ALL the code must be changed.
Stored In a table, the change is instantaneous thru the project.

[Ruegen]

does it matter what others do or don't do? It's what you want to do. I'm an accountant and I always store values as they are - 10% is 0.1 so I store 0.1

I want to integrate the database into other frameworks like ruby on rails etc, down the track etc. They sometimes have premade functions that prefer certain programmer standards and I wanted to find out what most people use - I like standards

Should not need to store this - it is easily calculated from the rate -priceinTax/(1+taxrate) assuming 0.1 rate not 10 rate and the 11 will change if the tax rate changes.

If the tax rate changes but I need to look up old invoices I will need to see for that time what the tax rate was etc and what the math was that used to go backwards for whatever reason.

When the tax rate changes so will the 11 division (whatever they choose to work backwards)

It's more for looking up old records but since I don't need to work backwards because I would be storing the pre GST/pre Tax price then I don't need to utilize the 11

Also does not need to be stored, it is a simple calculation GST=PriceTax<>PriceExTax

True I was just thinking from a database speed thing - not having to calculate it means making the database smarter...

I'd assumed you are creating the value and you were wondering about storing the tax rate in a table to reference when you wanted to calculate the tax on a sale. The normal policy for invoices is to store the net, gross and tax plus tax rate in the invoice so it cannot change.

But back to the tax table and why you need an effective date - say the taxman says that from 1st May 2015 the new rate is 12%. If you have a simple lookup in a table for the tax rate (or worse it is hard coded into your calculation) on the 1st May, first thing in the morning, not before, not after, you change the rate from 10 to 12 and off you go again. But on the 10th May, you realise you haven't completed some invoices for April - what you going to do, change the calculation back? run the invoices and then change it again?

Thanks heaps for your advice - I'm hoping the tax goes down not up We give them more money and they just find knew ways to blow the budget...

[Ruegen]

The storing of the rate in a table is for future change. If you code it, ALL the code must be changed.
Stored In a table, the change is instantaneous thru the project.

Thats what I thought too.

put simply having a null field vs a field with 0.1 is a bit scary for me

say for some reason someone edits a record and removes the 0.1 by accident - then I don't know if that record needs tax applied to it or not (some cases GST free)

Having the bool is a way for me to tell if GST was applied at all and if the field is null then I know something is up..

[CJ_London]

say for some reason someone edits a record and removes the 0.1 by accident - then I don't know if that record needs tax applied to it or not (some cases GST free)

I'm a bit lost as to how you are inputting data - this should be controlled by forms so the user never sees the table directly, and your argument doesn't make sense - the user could just as easily change the GST field and you have the same situation.

[CJ_London]

The storing of the rate in a table is for future change. If you code it, ALL the code must be changed.
Stored In a table, the change is instantaneous thru the project.

There are two tables in question. The one Ruegen is talking about is an invoice record, and you would store the calculated value and 'reference' data there - basically all that is required is the net amount and the tax amount - all the other values can be calculated from these two figures.

The one you (and I was originally) are talking about is a lookup table the form would use to get the rate applicable to the product/service and date of invoice.

I want to integrate the database into other frameworks like ruby on rails etc, down the track etc. They sometimes have premade functions that prefer certain programmer standards and I wanted to find out what most people use

Easily calculated 10= 0.1 * 100