Sql-server – Single Row Table for Storing an Additional Unique Identifier

database-designsql server 2014

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.

Best Answer

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.

Related Solutions

MySQL Auto-Increment – How to Have Two Auto-Increment Columns in One Table

What you are proposing to do can only be done with MySQL cleanly under three(3) conditions

CONDITION #1 : Use the MyISAM storage engine
CONDITION #2 : Make auto_increment column part of a compound primary key
CONDITION #3 : Each auto_increment for a given type must exist in its own row
See the auto_increment documentation for MyISAM

Here is your original table layout

CREATE TABLE `invoices` ( 
  `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  `invoicenumber` mediumint unsigned NOT NULL, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL 
) COMMENT='' ENGINE='InnoDB';

Based on the three conditions I just mentioned, here is the new proposed table layout:

CREATE TABLE `invoices` ( 
  `invoicenumber` mediumint unsigned NOT NULL auto_increment, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM';

Here is an example via sample data and SQL:

drop database if exists user1162541;
create database user1162541;
use user1162541
CREATE TABLE `invoices` ( 
  `invoicenumber` mediumint unsigned NOT NULL auto_increment, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM'; 
INSERT INTO invoices (branch,date,client) VALUES
('A',DATE(NOW()),'John'),
('B',DATE(NOW()),'Jack'),
('A',DATE(NOW()),'Jeff'),
('B',DATE(NOW()),'Joel'),
('A',DATE(NOW()),'Jane'),
('B',DATE(NOW()),'Joan'),
('A',DATE(NOW()),'June');
SELECT * FROM invoices ORDER BY branch,invoicenumber;

Here it is executed:

mysql> drop database if exists user1162541;
Query OK, 1 row affected (0.01 sec)

mysql> create database user1162541;
Query OK, 1 row affected (0.02 sec)

mysql> use user1162541
Database changed
mysql> CREATE TABLE `invoices` (
    ->   `invoicenumber` mediumint unsigned NOT NULL auto_increment,
    ->   `branch` enum('A','B') NOT NULL,
    ->   `date` date NOT NULL,
    ->   `client` varchar(100) NOT NULL,
    ->   PRIMARY KEY (branch,invoicenumber)
    -> ) COMMENT='' ENGINE='MyISAM';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO invoices (branch,date,client) VALUES
    -> ('A',DATE(NOW()),'John'),
    -> ('B',DATE(NOW()),'Jack'),
    -> ('A',DATE(NOW()),'Jeff'),
    -> ('B',DATE(NOW()),'Joel'),
    -> ('A',DATE(NOW()),'Jane'),
    -> ('B',DATE(NOW()),'Joan'),
    -> ('A',DATE(NOW()),'June');
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM invoices ORDER BY branch,invoicenumber;
+---------------+--------+------------+--------+
| invoicenumber | branch | date       | client |
+---------------+--------+------------+--------+
|             1 | A      | 2012-04-21 | John   |
|             2 | A      | 2012-04-21 | Jeff   |
|             3 | A      | 2012-04-21 | Jane   |
|             4 | A      | 2012-04-21 | June   |
|             1 | B      | 2012-04-21 | Jack   |
|             2 | B      | 2012-04-21 | Joel   |
|             3 | B      | 2012-04-21 | Joan   |
+---------------+--------+------------+--------+
7 rows in set (0.00 sec)

mysql>

Give it a Try !!!

CAVEAT : At present, only the MyISAM Storage Engine supports multiple auto_increment values grouped with other columns. This is not possible with InnoDB based on auto_increment columns being tied directly to the gen_clust_index (aka Clustered Index) !!!

Which tables to create according to relations

It isn't necessary to have a surrogate primary key on your clubs_chains table.

The combination of the two foreign keys in clubs_chains is adequate for the primary key.

You can use a foreign key constraint to ensure that your clubs_chains_paymethod table references an existing record in clubs_chains using the compound primary key. This might be helpful since it would allow more direct joins between clubs_chains_paymethod and your clubs and chains tables, without sacrificing any referential integrity.

On the other hand, some people just love using a surrogate primary key, even on intersection tables. If that is your data modelling style, then it's OK too.

Best Answer

Related Solutions

MySQL Auto-Increment – How to Have Two Auto-Increment Columns in One Table

Which tables to create according to relations

Related Question