Storing Constants

[ajarrell]

I will be using 3 rates in calculations in various queries. What is the right way to store the rates so that I am not "hard-coding" them into my calculations and can change them in one place should the need arise?

 

[pr2-eugin]

Table ! That's where you should be storing it.

 

[ajarrell]

OK. Should I have a table that looks like this:

ID Name Rate (field names)
1 LocalRate 1.15
2 NatlRate .55
3 AddRate .01

and do a lookup

OR

ID LocalR Natl R AddR (field names)
1 1.15 .55 .01

and reference by field name.

And how do I relate such a table in my database?

Thanks.

 

[pr2-eugin]

Normally it will be the First method as it stores the information in Rows rather than columns. Although in this case it makes more sense to use the second method.

The way you relate is, you create a new column (ratesID_FK) in the table where you have raw data, then add the ID of the new table (tblRates) there. So when you want the data you just use.

SELECT [COLOR=Blue]theRawData[/COLOR].FieldName, [COLOR=Red]tblRates[/COLOR].LocalR, [COLOR=Red]tblRates[/COLOR].NatlR, [COLOR=Red]tblRates[/COLOR].AddR
FROM [COLOR=Blue]theRawData [/COLOR]INNER JOIN [COLOR=Red]tblRates [/COLOR]ON [COLOR=Blue]theRawData[/COLOR].ratesID_FK = [COLOR=Red]tblRates[/COLOR].ID;

 

[gemma-the-husky]

the problem comes when the rates change. calculations that were done with the old rates will now use the new rates and will not be the same as they were before

so either -
a) store a history of rate changes OR
b) store the actual rate in the data table

and often both for important stuff like a VAT rate/Sales tax rate etc.