title: how to model discount on items in a database?
body: <p>I am building an ecommerce site and would like to offer discounts on certain items for a limited time.</p>
  
  <p>My Product table (MySQL) looks like this:</p>
  
  <pre>
  Product
      - productId
      - Name
      - Weight
      - Price (price as on the cover of the item)
  </pre>
  
  <p>Should I make another table for deals:</p>
  
  <pre>
  Deals
   - dealID
   - productID (Foreign Key)
   - discount (fractional value: percentage)
   - description
  </pre>
  
  <p>For retrieving items:</p>
  
  <ul>
  <li>q1: Find all products in products with productID = all the productIDs in deals table</li>
  <li>q2: Update the price with discount from the deals table</li>
  <li>q3: return all the products</li>
  </ul>
  
  <p>Is there a better way to do this ? Also, how do I handle the case of deal existing for only a limited time ?</p>
  
  <p>EDIT:
  I would like to display how much discount we are offering per product. Hence, I need two values per product, original price and the discounted price for the given duration.</p>
  
  <p>I posted a followup to the solution proposed by crontab <a href="https://stackoverflow.com/q/8578795/280924">here</a></p>