title: Database Design Brainstorming: Sale Prices
body: <p>I need to create a database solution to provide product discounting. </p>
  
  <p>Current tables:</p>
  
  <pre><code>Products
  Columns: ProductId, ProductTypeId, ReleaseDate
  
  ProductPrices
  Columns: ProductPriceId, ProductPriceTypeId (one product may have n prices), ProductId, Price
  </code></pre>
  
  <p>We want to be able to discount by ProductId and/or ProductTypeId and/or ProductPriceTypeId and/or ReleaseDate. </p>
  
  <p>Example sales:</p>
  
  <ol>
  <li>Discount a single ProductId.</li>
  <li>Discount all products of a specified ProductTypeId and
  ProductPriceTypeId.</li>
  <li>Discount all products of a specified ProductTypeId with a
  ReleaseDate within the last month.</li>
  </ol>
  
  <p>The challenging aspect of #2 is not the literal example, but considering long term scalability in the event new fields are added in the future.</p>
  
  <p>I am stumped how to handle #3 because of the ReleaseDate.</p>
  
  <p>Below, is what I mentally thought out before I realized I needed to come to Stackoverflow. You can see that the rigid structure will not allow for good scalability because of the explicitly included columns - if we added new criteria in the future then those columns would need to be added to the table - and not to mention it does not even handle the ReleaseDate requirement.</p>
  
  <p>New table:</p>
  
  <pre><code>ProductPriceDiscounts
  Columns: ProductPriceDiscountId, ProductPriceId, ProductTypeId, ProductPriceTypeId, Discount, DiscountTypeId (1 for percentage, 2 for fixed)
  </code></pre>
  
  <p>Then I could use something like this to get the pricing:</p>
  
  <pre><code>from p in Products
  join pp in ProductPrices on p.ProductId equals pp.ProductId
  let ppd = (
      from ppd in ProductPriceDiscounts
          .WhereIf(ppd.ProductPriceId != null, ppd.ProductPriceId == pp.ProductPriceId)
          .WhereIf(ppd.ProductTypeId != null, ppd.ProductTypeId == pp.ProductTypeId )
          .WhereIf(ppd.ProductPriceTypeId != null, ppd.ProductPriceTypeId == pp.ProductPriceId)
      select ppd).FirstOrDefault()
  where p.ProductId = productId
  select new 
  {
      ...,
      Price = pp.Price,
      Discount = pp.Discount,
      DiscountedPrice = 
          (ppd.DiscountTypeId == 1) ? 
              (pp.Price - (pp.Price * pp.Discount)) :
              (pp.Price - pp.Discount) :
  }
  </code></pre>
  
  <p>I only included this bad example I came up with to show the end result of how I need to be able to use this new product discounting functionality. Can anyone offer advice for a good way to handle this situation? Thanks.</p>