title: What is suggested database schema for order/invoice information?
body: <p>I have to make some changes to an e-commerce system to add some additional information and wanted to take the opportunity to possibly make some improvements and make it more flexible. When a customer places an order, we have to store several items of information with each item ordered; for example, the product price, the shipping price, the tax collected, any adjustments that were made.</p>
  
  <p>I am debating if these fields should be stored discretely, such as (simplified example):</p>
  
  <pre><code>ORDER_LINE_ITEM
      OrderLineItemID
      ProductID
      Qty
      Price
      Shipping
      Handling      
      SalesTax
      Adjustment
  </code></pre>
  
  <p>For example, I could then calculate the total price the customer paid:</p>
  
  <pre><code>SELECT Qty*(Price+Shipping+Handling+SalesTax) As TotalCollected FROM ORDER_LINE_ITEM
  </code></pre>
  
  <p>Or if I should use a more indirect structure:</p>
  
  <pre><code>ORDER_LINE_ITEM
      OrderLineItemID
      ProductID
      Qty
  
  ORDER_LINE_ITEM_ENTRIES
      OrderLineItemEntryID
      OrderLineItemID
      EntryType
      Value
  </code></pre>
  
  <p>For example:</p>
  
  <pre><code>1 | 1 | Price      | $10
  2 | 1 | Shipping   | $5
  3 | 1 | Handling   | $1
  4 | 1 | SalesTax   | $1
  5 | 1 | Adjustment | -$3.50
  </code></pre>
  
  <p>The advantage with this is that I can store additional information later on without altering the table schema. However retrieving the information and running reports becomes more complicated and slower.</p>
  
  <p>Is there a best practice for storing this information in order/invoice databases?</p>
  
  <p>Thanks in advance,</p>
  
  <p>Dan</p>