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

All Invoices Solutions on Gang of Coders

Total of 1 Invoices Solutions

Database design for invoices, invoice lines & revisions