Keeping a ratio column that's the division between two other columns

Question

I'm about to build a SQL table where I want to store currency orders. That means that I need to store how much I paid for a certain quantity, and the ratio between both quantities. So for example:

+-------------+--------------+-------+
| Quantity US | Quantity EUR | Ratio |
+-------------+--------------+-------+
|         250 |          200 | 1.25  |
+-------------+--------------+-------+

My question is: does it make sense to have a column storing a value that's always going to be the division between two columns, or is it a better practice to not store that in the DB and instead do the calculations every time I need the ratio?

Answer 1

In this case, it doesn't look like it makes sense - the calculation is simple, quick and easily made in application code (or even in SQL).

Adding the column means the storage for it is required, IO costs go up etc... Which is one cost/benefit analysis you need to make here.

If the calculation were something highly CPU intensive, denormalizing as you describe could make sense, in particular if you need to sort by this column.

Answer 2

It depends.

A bit of redundancy is allowed if comes with measured performance benefits. And this is if and only if you really need that performance boost, aka if it's a bottleneck in your application.

So is it really that difficult to compute the ratio in your code? In your specific example I don't feel like the additional redundancy is justifiable. In my experience it's very rare to have a good reason to add redundant columns.

In conclusion, from what I read in your question, no, you shouldn't add that column. Though I may not know the whole story.

Also, as others have mentioned, you can always use computed columns (in MySQL called generated columns) if you're really set on solving this in the database.

Answer 3

To my mind it isn't really data that needs to be stored. If you did store it of course, there is the complication that the ratio would need to be re-calculated should any of the underlying values change. Whatever way you do this will be messy - be it with stored procedures or triggers etc.

You don't say how this fits into the system overall but you could just derive the column for a standard CRUD scenario in the read SP or perhaps create a view to calculate it on the fly.

Answer 4

Ratio is a derived column that can be calculated. Store the value in database only for performance reasons. If your queries are slow. Otherwise do not store the derived column in the database.

If you are not going to store the value in the database, which seems the most reasonable in your case, I recommend the following two options:

1. If the calculation is simple you can use a view :

CREATE VIEW orders_view AS

SELECT QtyUS, QtyEU, QtyUS/QtyEU Ratio

FROM orders

2. If the calculation is complex you can use a view and a stored procedure :

CREATE VIEW orders_view AS

SELECT QtyUS, QtyEU, GetRatio(QtyUS,QtyEU) Ratio

FROM orders

If the value must be stored in the database, I highly recommend using insert/update database triggers for updating the derived column. This way you will ensure 100% correctness of data.