What is the best way to set a list of data on the same table id?

Question

I will try to walk you through the problem that I am facing so that you could understand how I got to this point.

This was my initial db:

The idea was to let one user update the value of a product. It was working fine though like this I can't record every update that the user has made because if that user updates the same product again it will just rewrite the last update and not write in the latest as intended. That was the case because the id's of user and product were matching on the two updates. So I tried to add another table just for the recording of updates like so:

Though the problem consists. Do I have to make another many to many relationship whose one end will be user_product or is there an more elegant way to resolve a problem like this?

Answer 1

you only need a "histiory"table that references

the productID and the changes made with timestamp and if more users are allowed to change the product also the userid As Id you have an autoincrement

When you want to know the latest changes You select all rows for that productid and sort it by timestamp Or only the price

like

history_products 

id
product_id 
alcohol
amount
mass
name
picture
price
timecreated

And use a select like

SELECT price,timecreated FROM history_products WHERE product_id = 10000 ORDER by timecreated;

You can insert automatically by using a trigger

DELIMITER $$

CREATE TRIGGER afterupdateproduct
    AFTER UPDATE
    ON products FOR EACH ROW
BEGIN
    INSERT INTO history_products (product_id,alcohol,amount,mass,name,picture, price,timecreated) 
    VALUES (OLD.alcohol,OLD.amount,OLD.mass,OLD.name,OLD.picture, OLD.price, Now());
END$$    

DELIMITER ;