title: ECommerce Storefront Website: Discovering Similar Products Programmatically
body: <p>I am developing a storefront web application. When a potential customer is viewing a product on the website, I'd like to suggest a set of similar products from the database automatically (vs requiring an human to explicitly input product similarity data/mappings). </p>
  
  <p>In fact when you think about it, most storefront databases already have a lot of similarity data available. In my case <code>Products</code> could be:</p>
  
  <ul>
  <li>mapped to a <code>Manufacturer</code> (aka <code>Brand</code>), </li>
  <li>mapped to one or more <code>Categories</code>, and </li>
  <li>mapped to one or more <code>Tags</code> (aka <code>Keywords</code>).</li>
  </ul>
  
  <p><img src="https://i.stack.imgur.com/IV6wG.jpg" alt="Storefront Datamodel Snippet"></p>
  
  <p>By counting the number of shared attributes between a product and all others, you could calculate a "SimilarityScore" for comparing other products against the one being viewed by the customer. Here's my initial prototype implementation:</p>
  
  <pre class="lang-sql prettyprint-override"><code>;WITH ProductsRelatedByTags (ProductId, NumberOfRelations)
  AS
  (
      SELECT  t2.ProductId, COUNT(t2.TagId)
      FROM    ProductTagMappings AS t1 INNER JOIN
                  ProductTagMappings AS t2 ON t1.TagId = t2.TagId AND t2.ProductId != t1.ProductId
      WHERE   t1.ProductId = '22D6059C-D981-4A97-8F7B-A25A0138B3F4'
      GROUP BY t2.ProductId
  ), ProductsRelatedByCategories (ProductId, NumberOfRelations)
  AS
  (
      SELECT  t2.ProductId, COUNT(t2.CategoryId)
      FROM    ProductCategoryMappings AS t1 INNER JOIN
                  ProductCategoryMappings AS t2 ON t1.CategoryId = t2.CategoryId AND t2.ProductId != t1.ProductId
      WHERE   t1.ProductId = '22D6059C-D981-4A97-8F7B-A25A0138B3F4'
      GROUP BY t2.ProductId
  )
  SELECT  prbt.ProductId AS ProductId
          ,IsNull(prbt.NumberOfRelations, 0) AS TagsInCommon
          ,IsNull(prbc.NumberOfRelations, 0) AS CategoriesInCommon
          ,CASE WHEN SimilarProduct.ManufacturerId = SourceProduct.ManufacturerId THEN 1 ELSE 0 END as SameManufacturer
          ,CASE WHEN SimilarProduct.ManufacturerId = SourceProduct.ManufacturerId 
              THEN IsNull(prbt.NumberOfRelations, 0) + IsNull(prbc.NumberOfRelations, 0) + 1
              ELSE IsNull(prbt.NumberOfRelations, 0) + IsNull(prbc.NumberOfRelations, 0)
          END as SimilarityScore
  FROM    Products AS SourceProduct, 
          Products AS SimilarProduct INNER JOIN
          ProductsRelatedByTags prbt ON prbt.ProductId = SimilarProduct.Id FULL OUTER JOIN
          ProductsRelatedByCategories prbc ON prbt.ProductId = prbc.ProductId
  WHERE SourceProduct.Id = '22D6059C-D981-4A97-8F7B-A25A0138B3F4'
  </code></pre>
  
  <p>which results in data like this:</p>
  
  <pre><code>ProductId                            TagsInCommon CategoriesInCommon SameManufacturer SimilarityScore
  ------------------------------------ ------------ ------------------ ---------------- ---------------
  6416C19D-BA4F-4AE6-AB75-A25A0138B3A5 1            0                  0                1
  77B2ECC0-E2EB-4C1B-A1E1-A25A0138BA19 1            0                  0                1
  2D83276E-40CC-44D0-9DDF-A25A0138BE14 2            1                  1                4
  E036BFE0-BBB5-450C-858C-A25A0138C21C 3            0                  0                3
  </code></pre>
  
  <p>I am not a SQL performance guru, so I have the following questions for you SQL gurus: </p>
  
  <ul>
  <li>Are <a href="/questions/tagged/common-table-expression" class="post-tag" title="show questions tagged &#39;common-table-expression&#39;" rel="tag">common-table-expression</a>s (CTEs) appropriate/optimum in this use case? (They sure seem to make it easier to read/follow the SQL). Is there a way to save a join in there anywhere given the model presented above?</li>
  </ul>
  
  <p>and</p>
  
  <ul>
  <li>Would this be a good candidate for an indexed view (for persistance) or would this add excessive cost to changes in source data? In that case I will make this a stored procedure which updates a physical <code>SimilarProductMappings</code> table for any given product.</li>
  </ul>