Self-Join relationship


#1

Hey All,

For an Ecommerce client with a LOT of products we need to be able to easily manage the upsell/cross-sell related products/items within HubSpot.

I believe the answer to this question will be "You can't" but I am hoping for someone to offer a good work-around.

Q: Is it possible to create a 'self-join' relationship in product table?
Purpose: To replicate the upsell/cross-sell products from the ecommerce site.

Otherwise, could this relationship be achieved with HubDB for integration into Emails?

@Walter_Anderson or @Sean_Duane, you might have some ideas here.


#2

Nick,

I'm not quite sure what self-join means here, sorry :x
Maybe you can provide an example of what functionality you're looking for?


#3

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.


#4

Nick,

I can expand the functionality of the foreign ID column to also be able to link to rows in the same table.

Would that satisfy your use case?

Thanks,
Matt


#5

Definitely worth testing, yes please! (And thank you!)


#6

@Matthew_Coley I will start testing this shortly, were you able to expand the functionality, or how will you let me know it's ready for testing?

Nick