I'm in the process of creating an application where we are fed several external product feeds daily, and we populate our products database with the feeds. However the trick is we need to keep the product db in sync with the latest feed(s).
Previously I had toyed with the theory of populating the current product list from db in an array, and doing array comparison with the latest feed, that got gunned down once the product count was in the thousands. (Ran out of memory when trying to get a 5000 records).
So after abit of research, it seems the solution would probably lie on the SQL side, using TRIGGERS perhaps. Though I'm not quite sure how to go about it, hence my question.
So the 2 objectives I need to accomplish with the syncing process:
1) Insert new products that do not already exist in our db. We can accomplish this with the INSERT IGNORE method.
2) Find products on our DB that do NOT exist on the latest feed, and do something to them. (flag as deleted, or move to a deleted products table, etc.)
Step 2 is where I'm having trouble. I'm thinking now maybe for every sync operation, we insert the products from the latest feed into a 'Temp-Products' table, and somehow compare 'Products Table' with 'Temp-Products' table in finding the records that need to be flagged as deleted.
Any advice please?
Thanks