Database design for invoices, invoice lines & revisions

Question

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 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'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 "Paid" status in the Invoices table if all the income related to a given job'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 "draft" 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'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...

4. Best way to define and track invoice status?

1. Draft
2. Issued
3. Voided

...constrained to change in one direction?

Answer 1

My advice from about 4 years of having to work with the back-end of an invoicing system that somebody else designed: Don't have a "pending" status on invoices. It will drive you insane.

The problem with storing pending invoices as ordinary invoices (with a "pending" flag/status) is that there will be hundreds of operations/reports that are only supposed to take into account posted invoices, which literally means every status except for pending. Which means that this status has to be checked every. single. time. And somebody is going to forget. And it will be weeks before anybody realizes it.

You can create an ActiveInvoices view with the pending filter built in, but that just shifts the problem; somebody will forget to use the view instead of the table.

A pending invoice is not an invoice. It is correctly stated in the question comments as a draft (or an order, request, etc., all the same concept). The need to be able to modify these drafts is understandable, definitely. So here's my recommendation.

First, create a draft table (we'll call it Orders):

CREATE TABLE Orders
(
    OrderID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED,
    OrderDate datetime NOT NULL
        CONSTRAINT DF_Orders_OrderDate DEFAULT GETDATE(),
    OrderStatus tinyint NOT NULL,  -- 0 = Active, 1 = Canceled, 2 = Invoiced
    ...
)

CREATE TABLE OrderDetails
(
    -- Optional, if individual details need to be referenced
    OrderDetailID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_OrderDetails PRIMARY KEY CLUSTERED,
    OrderID int NOT NULL
        CONSTRAINT FK_OrderDetails_Orders FOREIGN KEY
            REFERENCES Orders (OrderID)
            ON UPDATE CASCADE
            ON DELETE CASCADE,
    ...
)

CREATE INDEX IX_OrderDetails
ON OrderDetails (OrderID)
INCLUDE (...)

These are your basic "draft" tables. They can be changed. To track the changes, you should create history tables, which have all of the columns that are in the original Orders and OrderDetails tables, plus audit columns for the last modified user, date, and modification type (insert, update, or delete).

As Cade mentions, you can use AutoAudit to automate most of this process.

What you'll also want is a trigger to prevent updates to drafts that are no longer active (especially drafts that are posted and have become invoices). It's important to keep this data consistent:

CREATE TRIGGER tr_Orders_ActiveUpdatesOnly
ON Orders
FOR UPDATE, DELETE
AS

IF EXISTS
(
    SELECT 1
    FROM deleted
    WHERE OrderStatus <> 0
)
BEGIN
    RAISERROR('Cannot modify a posted/canceled order.', 16, 1)
    ROLLBACK
END

Since invoices are a two-level hierarchy, you need a similar and slightly more complicated trigger for the details:

CREATE TRIGGER tr_OrderDetails_ActiveUpdatesOnly
ON OrderDetails
FOR INSERT, UPDATE, DELETE
AS

IF EXISTS
(
    SELECT 1
    FROM
    (
        SELECT OrderID FROM deleted
        UNION ALL
        SELECT OrderID FROM inserted
    ) d
    INNER JOIN Orders o
        ON o.OrderID = d.OrderID
    WHERE o.OrderStatus <> 0
)
BEGIN
    RAISERROR('Cannot change details for a posted/canceled order.', 16, 1)
    ROLLBACK
END

This may seem like a lot of work, but now you get to do this:

CREATE TABLE Invoices
(
    InvoiceID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_Invoices PRIMARY KEY CLUSTERED,
    OrderID int NOT NULL
        CONSTRAINT FK_Invoices_Orders FOREIGN KEY
            REFERENCES Orders (OrderID),
    InvoiceDate datetime NOT NULL
        CONSTRAINT DF_Invoices_Date DEFAULT GETDATE(),
    IsPaid bit NOT NULL
        CONSTRAINT DF_Invoices_IsPaid DEFAULT 0,
    ...
)

See what I did here? Our invoices are pristine, sacred entities, un-sullied by arbitrary changes by some first-day-on-the-job customer service guy. There is no risk of screwing up here. But, if we need to, we can still find out the entire "history" of an invoice because it links back to its original Order - which, if you'll recall, we are not allowing changes to after it leaves the active status.

This correctly represents what's going on in the real world. Once an invoice is sent/posted, it can't be taken back. It's out there. If you want to cancel it, you have to post a reversal, either to an A/R (if your system supports that sort of thing) or as a negative invoice to satisfy your financial reporting. And if this is done, you can actually see what happened without having to dig into the audit history for each invoice; you just have to look at the invoices themselves.

There's still the problem that developers have to remember to change the order status after it's been posted as an invoice, but we can remedy that with a trigger:

CREATE TRIGGER tr_Invoices_UpdateOrderStatus
ON Invoices
FOR INSERT
AS

UPDATE Orders
SET OrderStatus = 2
WHERE OrderID IN (SELECT OrderID FROM inserted)

Now your data is safe from careless users and even careless developers. And invoices are no longer ambiguous; you don't have to be worry about bugs creeping in because somebody forgot to check the invoice status, because there is no status.

So just to re-summarize and paraphrase some of this: Why have I gone to all this trouble just for some invoice history?

Because invoices that haven't been posted yet aren't real transactions. They are transaction "state" - transactions in progress. They don't belong with your transactional data. By keeping them separate like this, you will solve a lot of potential future problems.

Disclaimer: This is all speaking from my personal experience and I have not seen every invoicing system in the world. I can't guarantee with 100% certainty that this is suitable for your particular application. I can only reiterate the hornet's nest of problems I've seen resulting from the notion of "pending" invoices, from mixing state data with transactional data.

As with every other design you find on the internet, you should investigate this as one possible option and evaluate whether or not it can really work for you.