Single Row Table for Storing an Additional Unique Identifier

Question

I just came across a somewhat old database (and frontend) and it has funny way of dealing with the aspect of unique ids.

I've got one table with a single column and row storing an Integer (currently 31448). This number is used on an Invoices table as 'InvoiceNo', the Invoices table also has a unique auto-inc id (currently 2847)

It looks a bit like this:

CREATE TABLE InvoiceNumber(
    LatestId [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE Invoices(
    InvoiceId [int] IDENTITY(1,1) NOT NULL,
    InvoiceNo [int] NOT NULL,
    Amount [decimal](18, 2) NULL
) ON [PRIMARY]
GO

No actual relationship between the two tables other than the InvoiceNo which is not actually an enforced relationship.

The frontend then, when the user adds a new invoice performs the following:

public static void CreateInvoice(Invoice newInvoice)
{
    try 
    {
        using (var ctx = new DataContext) 
        {
            var lastInvoice = (from lastNumber in ctx.SingleRowTable
                                    select lastNumber).Single;

            var newInvoiceNo = lastInvoice.InvoiceNumber +1; // Int

            lastInvoice.InvoiceNumber = newInvoiceNo;

            newInvoice.InvoiceNo = newInvoiceNo;

            ctx.Invoices.InsertOnSubmit(newInvoice);

            ctx.SubmitChanges;
        }
    }
    catch  (Exception x)
    {
        // Sleep now in the fire
    }
}

So basically when a new invoice is created the frontend requests the number on the InvoiceNo (single row) table and adds one (+1) that number then becomes the new invoice number and also the latest used invoice number.

Other tables in the database (InvoiceItems) reference the InvoiceId not the InvoiceNo.

I supposed the question is: Why would someone use this approach? is it an anti-pattern or design error or are there any real implementation towards it?

My thought was that for some reason the "Real World" invoice number needed to be preserved so they decided that running an additional 'Unique Id' would be the best way to deal with it. I thought maybe the developers couldn't afford a one thousand jump on the invoice number. (?) Maybe.

Answer 1

I used to work with a program that used a similar system to generate unique IDs for invoices. It did originate in a different database system, one without auto-incrementing IDs.

There's one difference with your system, and it's a difference I like a lot. The invoice table has a unique, auto-incrementing ID - and it has a separate, customer-facing (I'll assume) invoice number.

I went through a process of merging two structurally identical databases. The auto-incrementing ID values overlapped, so we had to reset one set of them as we went through the merge process. Our invoice table used the unique, auto-incrementing ID as the customer-facing invoice number. So, we functionally lost those invoice numbers in the system.

While it would have been potentially problematic to have two invoices with the same (customer-facing) invoice number, at least we would have had that option if the customer-facing value had been distinct from the primary key.

Answer 2

The solution is a 'normal' solution to generate a unique key. It is used when the database has no sequence or auto-increment option. You can also use it to become database independent.