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.

Answer 2

Typically invoice lines are not altered. i.e. an order (purchase order or work order) becomes an invoice. Once an invoice is issued, it can be voided or payments and credit memos can be applied, but that's usually about it.

Your situation may be a little different, but I believe this is the usual convention - after all, when you receive invoice xyz, you don't expect the data the document was based on to be altered in any way.

As far as the tax, typically in my experience, that is stored at the invoice level and determined at the time the invoice is posted.

As far as orders changing prior to becoming invoices, typically I've seen nothing more complex than basic database-level auditing - usually the application does not expose that history to users.

If you want a straighforward audit trail which is relatively domain-agnostic, you could look into AutoAudit - a trigger-based audit trail.

We typically don't have "draft invoices". It's tempting because you have a lot of similarity between orders and invoices. But in actual fact, it's better to have orders which haven't become invoices in a separate table. Invoices tend to have some differences (i.e. the state change is actually a transformation from one entity to another) and with referential integrity sometimes you really only want things joining to "real" invoices.

So we usually always have PurchaseOrder, PurchaseOrderLine, Invoice and InvoiceLine. In some cases, I have had the PO side behave more like a shopping cart - where the price is not stored and floats with the products table and other cases where they are more like price quotes that have to be honored once they are transmitted to the client. Those subtleties can be important when looking at the business workflow and requirements.

Answer 3

Why not just create copies of tables you want to audit and than on the original tables create triggres that will copy a row to table copies on every insert, update, delete?

The trigger usually looks something like this:

CREATE TRIGGER Trg_MyTrigger
   ON  MyTable
   AFTER UPDATE,DELETE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO [DB].[dbo].[MyTable_Audit]
           (Field1, Field2)
     SELECT Field1, Field2
    FROM DELETED
END
GO