So this is when we relate a table (in this case products) to itself.
If we want to recommend items that go well together (upsell/cross sell) then how we really want to structure in data is:
If customers purchased ‘Product A’ we could recommend ‘Product B and Product C’ as related items they might be interested in because they bought Product A.
All the details for Product B and Product C are already listed in the Product table. We don’t need to create an additional table to store information about these. In fact we absolutely shouldn’t as this would be a duplication/redundancy in our data.
Instead what we can do is add a field to our Products table which stores the ID’s of ‘related Products’; so in this was a relationship (self-join) is created to say that [Related Product ID] - [Product ID].
Or the more ideal world is one join for [Cross sell ID] and another for [upsell ID]. These both live on the Product table and refer to Product ID’s.
I am happy to jump on a call and discuss this if I still haven’t explained it well.