Replacing existent products

[ADDISON74]

Many months ago in a store that I manage on the lower level, the content manager decided without asking for any advice that the products that are no longer in the offer should be rewritten with new ones. Instead of creating a new product as was natural, he demanded that all Disable products be reused. This means that only ID remains from that product, the rest such as description, short description, attributes, pictures, price, options were redone from scratch. As expected, he recently received notifications on the grounds that the wish lists of some registered customers were altered, or they received price/stock notifications without having requested them.

In my opinion, this decision was a disastrous one because that product ID is unique and can be found in many places. For example, Related/Up-Sell/Cross-Sell, Wish Lists, Price & Stock alerts and many more. If you have ID = 5 for product X and you keep it and change it to product Y all the tables where ID = 5 will no longer be associated with X data but with Y data. The only way was to disable product X and after a while to delete it.

Seeing the negative magnitude of the changes, I was asked to restore their database only for the products as it was a few months ago, but I don't know exactly when the first change was made. Also, we are talking about months and things happened in the meantime, what do we do with these changes? My opinion that the harm has been done and nothing can be done for the moment. At least in case of a wish list or a price alert I do not know if those products referred to are the ones assigned by the customer or not. The first option is to stop this practice immediately and then in 1-2 years the products will be corrected. In relation to the registered customers, wish lists and notifications could be an immediate problem for a while.

[justinbeaty]

That sounds terrible! Theoretically, you could sort all of this out, because at least tables like wishlist_item include the column added_at with a timestamp, so anything before is the old product and anything after is the new one. What's worse is that sales_flat_order_item contains a product_id reference, so these new products would look as if they've been sold before they existed, if you ever had to run some reporting. The admin view on old orders is probably okay, since that table also stores the product name, sku, etc, but the underlying information is wrong.

I recently did a migration where I imported all the products into a fresh database, and used a script to migrate orders over (while mapping old skus -> new skus, and old product_id -> new product_ids), so it's possible to write a script to sort all this out, it just sounds like a major headache, and I'd be charging this client double at least.

[ADDISON74]

I did an deeper investigations. Products ID's appear in many places but sometimes their data are stored in tables as they were at a time snapshot. At least for Orders, Invoices, Shipments I found the correct ordered products. Issues occur with Price/Stock Alerts, Wish List. Also with Related/Up-sells/Cross-sells. Let's say that for the latter they can be checked one by one, but for those established by registered customers it is very difficult to make an evaluation. Any alteration of the data for a possible fix can create new situations.

In order to check the accuracy of the information I should rely on the date of updating the product and adding it to the wish list or alerts. But I can't know for sure if they are real. Let's say we had product X on 01.01.2021 and it was added to a wish list. After the date there are updates of it. Now I have to determine if it is a replacement with product Y or just a modification of product X.

To do a checking job at such a volume of data is really crazy (160.000 simple products). People need to understand that they can't take bricks out of a building and put in others. In addition, there is a hypothesis that this content manager may have operated other changes in the past. Better leave things like that and if the store receives complaints they should find a plausible an honest excuse (e.g. We are a little bit stupid).

Please tell this story to those who create content in Magento/OpenMage not to make the same mistake of replacing already created products. All they have to do is deactivate the ones they can no longer offer and after a period of 6-12 months to delete them permanently. You can have control of a store only if you are the only one who changes it, otherwise you never know what to expect.

[justinbeaty]

Yeah, if you can't reliably figure out what was changed when, I'd not try and fix this situation. What a mess!

And yeah the sales_flat_* tables hold the snapshot of the product info at the time of sale, which is I'm not sure is the best feature, but in this case it saved your client. The only problem I mentioned is if you try and run some custom reporting, or if there's any reporting modules installed, they could report wrong info. Depends on if the report uses the product_id or the sku to look up data from the table.

Anyway, thanks for sharing the tale, I can't imagine why the content manager thought that was a good idea.

[ADDISON74]

You made a very good observation. Also Reports related to products could be affected. If there is a report for product X until the date it was replaced all reports related to Y will be added to the ones from X. Certainly if we analyze more we will see the appeal of a really bad decision. That's why I don't even want to lift a finger for them but it remains an experience that I will not forget long time.

[ADDISON74]

A snapshot of the product data is a good practice. We keep information about what someone ordered in the past. But once the product is disabled or deleted from database there should be no links in Customer Account that point to it. I never checked this scenario but I will make time for it.