MySQL table setup counting a fields daily value changes

Question

I have a table which at the moment just consists of an id, name, and number field. I would like to be able to record how much number field changes daily so that I may graph it later(ex. 10/01 5 , 10/02 20 etc). How do I set up the table in such a way that it will keep up with the daily changes?

Answer 1

In my opinion what you are trying to do should be stored in another table

id, refId, delta, timestamp

Where refId is the id of the item in the table you described, delta is the change in number and timestamp is the time of the change.

Answer 2

My recommendation is to add only one column to your existing table called timestamp - a DATETIME data type. Then you just calculate whatever metric want you, using aggregate functions (COUNT, SUM, etc) based on the grouping.

For example, assuming number values are negative if there's a decrease, you can get the delta using:

  SELECT t.id,
         t.name, 
         SUM(t.number) AS delta
    FROM YOUR_TABLE t
GROUP BY t.id, t.name, DATE(t.timestamp)

You could change that to see what happens by year, month, etc. Without the mess of needing additional logic to calculate the values...