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
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 ;
You can add another column named like is_active ENUM type with value active,inactive
By default is_active's value will be 'active'
and when you enter new entry in database just update old entry's is_active with 'inactive' and then add new entry
Fetch new data using query using where clause WHERE is_active='active'