Store timestamp and their price values

I have the following mysql table “auctions”. The “quantity available”, “price” and the “current timestamp” will be updated every hour from an extern API.

item_id | item_class | item_subclass  | quantity vailable  | price | current timestamp
--------------------------------------------------------------------------------------
        |            |                |                    |       |

I want to store these datas for a price/ quantity archive. For that I need to archivate the older datas (quantity available, price and their timestamps- the “item_id” should be the access column). I think I need to create another table but I don´t know whats the best way to structure it, since it need to be updated every hour with old datas.

-> Whats the best way to safe timestamps and their respective values (like the price), so they don´t get updated every hour.

Answer

If you are updating this table with the ‘item_id’ as the primary key, you need to create another table with one more column say backup_id (this will be the primary key for the second table). After every hour when you are going to “update” mysql table ‘auctions’, “insert” the same entry in the second table.