Sql – Database Design for Revisions

There are a few steps to see the tables in an SQLite database:

1. List the tables in your database:

   .tables
   

2. List how the table looks:

   .schema tablename
   

3. Print the entire table:

   SELECT * FROM tablename;
   

4. List all of the available SQLite prompt commands:

   .help
   

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.