MySQL存储：存档数据的最佳方式

Question

I'm quiet new in the SQL field. Thus I have a way of working question.

Every week, I will send data from an Excel spreadsheet on my MySQL DB through a pHp code. This is already working. Thus I have a table which I can update.

Actually I'm sending price of a specific underlying on my DB. What is the best way to archive my data as following.

On my DB, I have the below structure :

tabe t_index
Label = VARCHAR(255)
Price = float
Date = date()

Let's say I sent my data on my db last week, thus I stored :

Stock A
102.85
2013-03-18

Today, I want to send new price for the same Stock A which is 103.54, but I would like to archive and keep the 102.85 price to be able to make some return or whatever between the two prices.

How should I proceed ?

I hope my question is not too messy... Thank you for your help

Answer 1

One way of doing this is, create a UPDATE trigger which inserts old value in another table. So when you update an existing entry, old data will be copied/archived to another table.

CREATE TABLE t_index_archive (
Label VARCHAR(255),
Price float,
Date datetime);

Now create a trigger on your existing table

DROP TRIGGER IF EXISTS archive_t_index;
DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER archive_t_index BEFORE UPDATE ON t_index
     FOR EACH ROW BEGIN 
          INSERT INTO t_index_archive VALUES (OLD.Label, OLD.Price, OLD.Date);
     END;
$$
DELIMITER ;