MySQL - should I include columns for calculation results

daymobrew

When I fill up the car with petrol I note the odometer and num of litres. This way I can calculate mpg.
I am putting this info into a MySQL table. I've created columns for date, odometer, #litres, unit cost, total cost, miles per litre and miles per gallon.

When adding a row I will prompt for date/odo/#litres/unit cost. The rest can be calculated easily.

Questions:
1) Is it good practice to put the derived data into the table?
2) Are there MySQL features where I can specify that a column is based on another column e.g. 'total cost = #litres * unit cost'?

Baz_

1) Generally I believe it's considered bad database design, the norm is to write a query to find out any information that you derive from information already stored in the database.

2) I don't know for certain, but since it's considered bad database design I very much doubt it.

Hope that helps.

Baz_

Cake Fiend

I think you can use triggers to do this in other DBSs, but not in MySQL AFAIK. I gather you're generally expected to be using MySQL through an API for the most part, so it's left as an exercise for the program/script you're using to do the appropriate calculations before inserting anything.

Edit: In the above post, I assumed you were using MySQL from the CLI and wanted to insert the calculated data with the rest of the data in a single statement. Obviously if you're using an API, you can do it no problem, or you could use two seperate SQL statements (insert known data first, then use fatlog's suggestion second).

fatlog

you can derive a value form other columns in mysql.

SELECT (units*litres) as totalCost FROM ...

madramor

when they talk about not storing dervied data its usually
involves complex calculation with a lot of variables

eg(if loan amount > 1000000 then use ecb + standard rate - discount)

in this case there are no other variables apart from the basic
imputs and the formula never changes.

so i would have e = entered c = calculated
(e)date - date
(e)odometermile - float
(c)odometerkm - float
(e)litres - float
(e)unit cost - float
(c)total cost -float
(c)kpl - float
(c)mpg - float

easy if you want to search for:
what date did you spend the most
what date did you get best kpl,mpg

Hellm0

Yes its kind of bad practise really,but who cares.
If the formula remains a constant, and your using enterprise manager, just go to "Formula" on your column design menu and enter it in,
eg)

The formula for DateOfExpiry would look like this in the formula textbox.

([DateOfIssue] + 28)

The other way to do it would be using a stored procedure, selecting the values used in the formula, working it out then updateing the field which holds the output.