title: Modeling Data - Invoices and Line Items
body: <p>I'm creating a web based point of sale (think cash register) solution with Django as the backend. I've always taken the 'classic' approach of modeling invoices and their line items.</p>
  <pre><code>InvoiceTable
    id
    date
    customer
    salesperson
    discount
    shipping
    subtotal
    tax
    grand_total
    [...]
  
  InvoiceLineItems
    invoice_id // foreign key
    product_id
    unit_price
    qty
    item_discount
    extended_price
    [...]
  </code></pre>
  <p>After attempting to research best practices, I've found that there aren't many - at least no definitive source that's widely used.</p>
  <p>The Kimball Group suggests: &quot;Rather than holding onto the operational notion of a transaction header “object,” we recommend that you bring all the dimensionality of the header down to the line items.&quot;</p>
  <p>See <a href="http://www.kimballgroup.com/2007/10/02/design-tip-95-patterns-to-avoid-when-modeling-headerline-item-transactions/" rel="nofollow noreferrer">http://www.kimballgroup.com/2007/10/02/design-tip-95-patterns-to-avoid-when-modeling-headerline-item-transactions/</a> and <a href="http://www.kimballgroup.com/2001/07/01/design-tip-25-designing-dimensional-models-for-parent-child-applications/" rel="nofollow noreferrer">http://www.kimballgroup.com/2001/07/01/design-tip-25-designing-dimensional-models-for-parent-child-applications/</a>.</p>
  <p>I'm new to development (only having used desktop database software before) - but from my understanding this makes sense as we can drill down the data any way we want for reporting purposes (though I imagine we could do the same with the first method by joining the tables).</p>
  <p><strong>My Questions</strong></p>
  <ul>
  <li><p>The invoice ID will need to be repeated for each row (so we can generate data like totals for the invoice). Is this an intentional feature of this way of modeling the data?</p>
  </li>
  <li><p>We often have invoice level data like notes, discounts, shipping charges, etc. - How do we represent these using this method? Some discounts are product specific - so they belong on the line item anyway, others are invoice wide (think of a deal where you buy two separate products and receive a discount on the two) - we could we somehow allocate it across the line items? Same with shipping charges, allocate it by dividing it among the line items?</p>
  </li>
  <li><p>What do we do with invoice 'notes' - we have printed and/or internal notes, would we put the data in the line items and just repeat it for each line item? That seems to go against data normalization. Put it in a related table?</p>
  <p>Any open source projects that use this method that I could take a look at? Not sure how to search for them.</p>
  </li>
  </ul>