Strategy in storing ad-hoc numbers/constants?

Question

I have a need to store a number of ad-hoc figures and constants for calculation. These numbers change periodically but they are different type of values. One might be a balance, a money amount, another might be an interest rate, and yet another might be a ratio of some kind.

These numbers are then used in a calculation that involve other more structured figures.

I'm not certain what the best way to store these in a relational DB is - that's the choice of storage for the app.

One way, I've done before, is to create a very generic table that stores the values as text. I might store the data type along with it but the consumer knows what type it is so, in situations I didn't even need to store the data type. This kind of works fine but I am not very fond of the solution.

Should I break down each of the numbers into specific categories and create tables that way? For example, create Rates table, and Balances table, etc.?

Answer 1

Yes, you should definitely structure your database accordingly. Having a generic table holding text values is not a great solution, and it also adds overhead when using those values in programs that may pull that data for some calculations.

Keeping each of the tables and values separated allows you to do things like adding dates and statuses to your values (perhaps some are active while others aren't?) and also allows you to keep an accurate history (what if i want to see a particular rate from last year?). It also makes things easier for those who come behind you to sift through your data.

I suggest reading this article on database normalization.