Database design for invoices, invoice lines & revisions
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.
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))
LineId (int) // Primary key InvoiceId (int) // related to Invoices above Quantity (decimal(9,4)) Title (nvarchar(512)) Comment (nvarchar(512)) UnitPrice (smallmoney)
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
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.
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?
…constrained to change in one direction?
4 Solutions collect form web for “Database design for invoices, invoice lines & revisions”
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
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
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.
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.
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
I agree with Aaronaught’s comment above regarding the “immutability” of the invoice.
If you take that advice, then I’d consider having “Pending Review,” “Approved,” and “Voided” as statuses. “Pending Review” is just that. “Approved” is deemed to be correct, and payable by client. “Voided” is just that: invoice is no longer valid, and not payable by client. Then you can deduce whether the invoice is paid in full from the records in
Payments, and you’re not repeating information.
Aside from that, no real issues with your revision idea though.
You can include tax as just another record in