Woocommerce-admin: Products: Add Product Name to Lookup Table

[timmyc]

Follow-Up to #1669

Per our discussion in last week's hangout, while #1750 does fix the exception when a Product is deleted, we are hoping to implement another solution to ensure a better user experience with a product is deleted.

After more discussion on this, I think we should move ahead with the solution @rrennick posted:

An alternative I suggested in the hangout was looking up the product name in the order meta when the product had been deleted. There is a bit of overhead for this but as long as it's written so that the lookup only occurs when the product can't be found then it will only affect a small number of queries.

_leaving old idea here just for reference_

The plan discussed is to cache product names in wc_order_product_lookup so if a product is ever deleted, we can still display the name of the product as it was titled at the time of the order.

[timmyc]

/cc @peterfabian does this seem like a reasonable approach to cache the product names in the lookup table to get around the issues created when a product is deleted?

[joshuatf]

I think based on @peterfabian's previous comment that he is not super keen on the idea, but I don't want to put words in his mouth.

After thinking about it, I also agree that if you choose to permanently delete a product, you do lose some data associated with that product. I was AFK for the last hangout though- is there new information or a use case for keeping this deleted info?

If we do choose to start storing deleted names in the tables, I have a few questions on implementation.

Do we need to start doing this for other lookup tables as well?

• Coupons
• Categories
• Customers

Re-syncing data
On data re-sync, we will not have access to the actual product name from the posts table. We can still grab the product name from the wc order item meta, but this may vary within the same product if the name was ever changed.

Display of deleted products
If we're storing product names in the lookup table, do we want to just append (Deleted) to the end of the product name? (e.g., Cool Hoodie (Deleted)) I imagine it may otherwise be confusing to users why certain products only have a name and are missing other product information. /cc @LevinMedia

[timmyc]

David was the one who was most opinionated on the matter, so I'll wait for him to chime in as well.

I suppose I see both sides to the argument. As a personal store operator, I'd always choose the path of least destruction and simply deactivate a product vs delete.

The only real dilemma I see here is orders with the deleted product will still show the deleted product name, so we aren't really following suit there. That's why I can personally see why taking this approach makes sense.

Granted we could dip into the order meta when a deleted product is encountered for reporting, that could be a significant speed hit.

Meeting notes for reference are at p90Yrv-Zu-p2

[joshuatf]

The only real dilemma I see here is orders with the deleted product will still show the deleted product name, so we aren't really following suit there. That's why I can personally see why taking this approach makes sense.

That makes sense.

Granted we could dip into the order meta when a deleted product is encountered for reporting, that could be a significant speed hit.

This does seem like it will be a pretty large performance issue. If we choose to store them in the table, this at least isn't happening on the fly, but we'll need to account for the fact that the name could be different across different orders (we'd probably want to display the product name that was stored under the newest order in the products report).

[peterfabian]

What I don't like about this solution is that not only the names will be duplicated with the meta table, but if we add them to wc_order_product_lookup, the product name will be stored every time the product appears in an order as line item. That is quite bad imho, as we just need product_id->name lookup (unless we want to store other product properties like price, categories, etc).

Maybe it has other drawbacks, but how about a small (I assume) lookup table for names of deleted products? If the product requested by id does not exist, look up just the name in the deleted products table, otherwise work as usual?

[peterfabian]

Other solution could be to add 'deleted' status to products and just leave them in posts. Not sure if that would be a problem, perhaps it has some potential to cause unexpected side effects somewhere else?

[timmyc]

Maybe it has other drawbacks, but how about a small (I assume) lookup table for names of deleted products?

Seems like that could work well too.

[LevinMedia]

Could that lookup table be extended to other deleted things like coupons, categories, tax codes etc, or would each of those things require it's own table?

[joshuatf]

Maybe it has other drawbacks, but how about a small (I assume) lookup table for names of deleted products? If the product requested by id does not exist, look up just the name in the deleted products table, otherwise work as usual?

I think you're on the right track here. Instead of creating a lookup table for deleted products, could we map out deleted product IDs and only grab those from the order item meta table? Would that reduce the performance impact of running the meta join on all products?

Other solution could be to add 'deleted' status to products and just leave them in posts.

This feels like the cleanest and easiest solution, but I think this might be problematic long-term. Products never truly being deleted may cause side effects and could cause some plugins that rely on the deletion of products to fail.

Could that lookup table be extended to other deleted things like coupons, categories, tax codes etc, or would each of those things require it's own table?

In terms of storing data in each, it just depends on whether or not we have a lookup table for that item:

• Coupons - Yes
• Categories - No
• Tax codes - Yes

However, I think this is going to create a lot of bugs and performance issues with wc-admin, so I'm with Peter on this and think we should avoid data duplication.

However, without creating new tables for each and effectively storing all WC data twice across different tables, we won't be able to grab the data on the individual reports (e.g., we could look up a deleted tax code for an order, but we would not be able to stats for that tax code on the taxes report).

[rrennick]

An alternative I suggested in the hangout was looking up the product name in the order meta when the product had been deleted. There is a bit of overhead for this but as long as it's written so that the lookup only occurs when the product can't be found then it will only affect a small number of queries.