title: DB Design Questions for eCommerce
body: <p>Say I have 2 "things" I can sell: <code>service</code> and <code>product</code>. Each is represented with a table that captures their unique attributes (e.g. service might have a per hour rate while product may have a per unit price, etc.).</p>
  
  <p>Each time a sale is made, assume that I need to capture the exact same data about the sale regardless of whether the service or product is sold. How do I model this?</p>
  
  <p><strong>APPROACH 1:</strong></p>
  
  <ol>
  <li>I create one table to hold the details of each sale</li>
  <li>I create two "mapping" tables to connect the product or service with the sale table</li>
  </ol>
  
  <p>So, something like this:</p>
  
  <pre><code>TABLE: product
  - product_id (PK)
  
  TABLE: service
  - service_id (PK)
  
  TABLE: sale
  - sale_id (PK)
  
  TABLE: product_sale
  - product_id (FK)
  - sale_id (FK)
  
  TABLE: service_sale
  - service_id (FK)
  - sale_id (FK)
  </code></pre>
  
  <p><strong>APPROACH 2:</strong></p>
  
  <p>Skip the mapping tables and just have separate tables to record sales for products and services.</p>
  
  <pre><code>TABLE: product
  - product_id (PK)
  
  TABLE: service
  - service_id (PK)
  
  TABLE: product_sale
  - product_sale_id (PK)
  - product_id (FK)
  
  TABLE: service_sale
  - service_sale_id (PK)
  - service_id (FK)
  </code></pre>
  
  <p>I feel approach 1 would be better since I would need to generate reports like:</p>
  
  <ul>
  <li>Total sales for a given period</li>
  <li>Total sales per sales person</li>
  <li>etc.</li>
  </ul>