Implementing Recommendations/Cross Sells

I need the ability for site admins to create and manage product recommendations. Products can be recommended at a store level, within 0..n categories or for 0..n specific products. So, if you are looking anywhere in a store or cart, a sports store might want to recommend some kind of gift card. If you are in the tennis racket category, you might want to recommend tennis balls, and if you're looking at the product detail for a specific camera (or a cart containing it), you might want to try to recommend some batteries and a carrying case. You might well want recommendations to be bi-directional with the ability to have a "Recommend this product for the following products" field for products but also to have a "Recommend the following products when viewing this product" option.

I'm looking to implement this from an administrators perspective by adding four specific properties to my admins:

• Product.RecommendAlways - If set to yes, this product will always be recommended.
• Product.RecomendinCategoryList - A multiple select list of categories in which this product should be recommended.
• Product.RecommendforProductList - A multiple select list of products to recommend this product for.
• Product.RecommendedProductList - A multiple select list of products to be recommended when viewing this product.
• Category.RecommendedProductList - A multiple select list of products to recommend in this category.

Product.RecommendforProductList and Product.RecommendedProductList should be interdependent across the catalog. If I go into tennis balls and say they should be recommended for a given tennis racket by adding them to Product.RecommendforProductList, if I then go into edit that tennis racket, I'll see the tennis balls preselected in the Product.RecommendedProductList multiple select. (Please note all of these approaches assume a fairly small product base - a drop down list with 20,000 products would be ugly! Does anyone have any good UI approaches to managing recommendations where there are tens or hundreds of thousands of products in a system? Pop up windows with searching and filtering product lists to populate a text box or hidden field? Wizards? Separate recommendation admin as opposed to building it into the product admin?).

There would be four main places where you'd need to return recommendations. General pages, category detail pages, product detail pages and cart pages.

• General Page - Displays a list of recommended products where Product.RecommendAlways = 1
• Category Detail - Displays a list of recommended products where Product.RecommendAlways = 1 OR where Category.RecommendedProducts includes this product
• Product Detail - Displays a list of recommended products where Prodct.RecommendAlways = 1 OR Product.RecommendedProductList includes this product (which will automagically pick up any products where you added this product to the recommend for product list)
• Cart - Same as product detail, but for all products within the cart

There are lots of ways of implementing this, but the key to all of the performant ones is unpacking of the lists (Category.RecommendedProducts, and the interdependent Product.RecommendedProductList and Product.RecommendforProductList).

The category recommendations are fairly straightforward. We could have a tbl_ProductCategoryRecommendation with CategoryID and RecommendedProductID so a simple inner join using tbl_ProductCategoryRecommendation would return all of the products recommended for a category.

The product recommendations are more interesting (this relates to a recent article by Steve Bryant and a feature we'll be seeing in the excellent DataMgr 2.1).We need a tbl_ProductProductRecommendation with ProductID and RelatedProductID, but whenever we select or delete from the table it needs to be conceptually where "ProductID = #ThisID# OR RecommendedProductID = #ThisID#".

This seems to me to be a useful approach to recommendations, but I'd love to hear what everyone else is doing!