title: Database design for invoices, invoice lines & revisions
body: <p>I'm designing the 2nd major iteration of a relational database for a franchise's CRM (with lots of refactoring) and I need help on the best database design practices for storing <strong>job invoices</strong> and <strong>invoice lines</strong> with a strong <strong>audit trail</strong> of any changes made to each invoice.</p>
  
  <h1>Current schema</h1>
  
  <h2><code>Invoices</code> Table</h2>
  
  <pre><code>InvoiceId (int) // Primary key
  JobId (int)
  StatusId (tinyint) // Pending, Paid or Deleted
  UserId (int) // auditing user
  Reference (nvarchar(256)) // unique natural string key with invoice number
  Date (datetime)
  Comments (nvarchar(MAX))
  </code></pre>
  
  <h2><code>InvoiceLines</code> Table</h2>
  
  <pre><code>LineId (int) // Primary key
  InvoiceId (int) // related to Invoices above
  Quantity (decimal(9,4))
  Title (nvarchar(512))
  Comment (nvarchar(512))
  UnitPrice (smallmoney)
  </code></pre>
  
  <h1>Revision schema</h1>
  
  <h2><code>InvoiceRevisions</code> Table</h2>
  
  <pre><code>RevisionId (int) // Primary key
  InvoiceId (int)
  JobId (int)
  StatusId (tinyint) // Pending, Paid or Deleted
  UserId (int) // auditing user
  Reference (nvarchar(256)) // unique natural string key with invoice number
  Date (datetime)
  Total (smallmoney)
  </code></pre>
  
  <h1>Schema design considerations</h1>
  
  <h2>1. Is it sensible to store an invoice's Paid or Pending status?</h2>
  
  <p>All payments received for an invoice are stored in a <code>Payments</code> table (eg. Cash, Credit Card, Cheque, Bank Deposit). Is it meaningful to store a "Paid" status in the <code>Invoices</code> table if all the income related to a given job's invoices can be inferred from the <code>Payments</code> table?</p>
  
  <h2>2. How to keep track of invoice line item revisions?</h2>
  
  <p>I can track revisions to an <strong>invoice</strong> by storing status changes along with the invoice total and the auditing user in an <strong>invoice revision table</strong> (see <code>InvoiceRevisions</code> above), but keeping track of an invoice line revision table feels hard to maintain. Thoughts? <strong>Edit:</strong> line items should be immutable. This applies to a "draft" invoice.</p>
  
  <h2>3. Tax</h2>
  
  <p>How should I incorporate sales tax (or 14% VAT in SA) when storing invoice data?</p>
  
  <hr>
  
  <p><strong>Edit:</strong> Good feedback, guys. <strong>Invoices and invoice lines are by definition immutable</strong>, so tracking changes isn't sensible. However,  a "draft" invoice must be editable by more than one person (eg. manager applies discount after technician creates invoice) before it is issued...</p>
  
  <h2>4. Best way to define and track invoice status?</h2>
  
  <ol>
  <li>Draft</li>
  <li>Issued</li>
  <li>Voided</li>
  </ol>
  
  <p>...constrained to change in one direction?</p>