MySQL table design

Question

Hi all,

I have a table with products. Each product has a title and a price.

The products come in huge XML files, on a daily basis.

I store all of them in MySQL. But sometimes they have a wrong title. But i can't edit it, because they will be lost the next day (cronjob removes all products and inserts again).

What would be the best way to edit them? Save them in a different table and SELECT both tables at once? Whereas the table that contains the edited rows has precedence over the cronjob table.

What would be the best way to handle it, since there are 300.000+ products. Products might be (manually) edited via a CMS system.

Thanks!

Answer 1

If there is a unique identifier for each product that remains constant over updates, you could make a table containing the product ID and the corrected title. Correcting a title would involve inserting a row into this table as well as updating the main table.

As the last step of the cron job, you can then update your main table of products from this one.

UPDATE FROM tblProduct p, tblProductCorrections pc
SET p.strTitle = pc.strCorrectedTitle
WHERE p.intId = pc.intProductId

Answer 2

Is there some sort of ID that remains constant? (productID) for example?

Can you edit the cronjob?

If both of the above is true; i'd edit the job to only add new records into the table; preventing writing over your updated values.