title: Invoice design - per item price, tax, and discount
body: <p>Not being much of a business-person, I'm unsure how I should design the data structure (both in-memory and the database schema) for invoices in my application (which handles accounts receivable from clients).</p>
  
  <p>My question concerns invoice line-items. It's already a given that items have a name or textual description, a price-per-unit, and a quantity value (thus the price-per-unit multiplied by the quantity gives the line total). However I don't know how I should factor-in per-line discounts and taxes, especially when both can be expressed as a percentage or as a fixed amount, and then I need to consider the order of operation (is a fixed-price discount done before or after a percentage tax increase?).</p>
  
  <p>Here's the DB schema I'm thinking:</p>
  
  <pre><code>InvoiceItems
      InvoiceId        bigint
      ProductId        bigint NULL       -- Optional reference to the product this item is generated from
      Description      nvarchar(255)
      PricePerUnit     money
      Quantity         decimal(9,4)
      AdjustmentBT     money NULL        -- before-tax fixed-value price adjustment 
      AdjustmentBTPerc decimal(9,4) NULL -- before-tax percentage price adjustment
      Tax              decimal(9,4) NULL -- tax as a percentage
      AdjustmentPT     money NULL        -- after-tax fixed-value price adjustment 
      AdjustmentPTPerc decimal(9,4) NULL -- after-tax percentage price adjustment 
  </code></pre>
  
  <p>So the line-total is this function:</p>
  
  <pre><code>LineTotal = ( ( ( ( ( PricePerUnit * Quantity ) + AdjustmentBT ) * AdjustmentBTPerc ) * Tax ) + AdjustmentBT ) * AdjustmentPTPerc
  </code></pre>
  
  <p>Or in RPN:</p>
  
  <pre><code>LineTotal = PricePerUnit Quantity * AdjustmentBT + AdjustmentBTPerc * Tax * AdjustmentBT + AdjustmentPTPerc *
  </code></pre>
  
  <p>As I'm not someone who works with invoices at all, and with limited feedback from the person I'm writing this program for, I don't know if I'm over-thinking it or not. I need to provide sufficient flexibility but without being complicated - and with this approach it means each invoice item is going to look like this:</p>
  
  <pre><code>Description | PricePerUnit | Quantity | Before-tax Adjustment | Tax | Post-tax Adjustment | %computedTotal%
  </code></pre>
  
  <p>...where the Adjustment fields interpret the entered value as a percentage or fixed value depending on the presence or absence of the '%' character.</p>