Are sequential numbers necessary?

Question

I am working on a winform (.NET) application which includes Orders, Invoices, Service Orders, Ticketing etc.

It it necessary for these enities to be sequential when numbering their IDs? IMO no. Take an order for instance, it can only be valid once it passes thorugh the business layer, during that proocess another order could've been created, approved and saved with the number 2 while the order which was created earlier with id 1 failed validation.

This seems to open a can of worms as to which layer assigns the order number, no?

Currently I am using non-sequential numbers prefixed with an identifier for the entity. Example, order uses OR-123. Is this a good idea?

Thanks

Related:

Database-wide unique-yet-simple identifiers in SQL Server

Mclin

Answer 1

In my experience with off-the-shelf and bespoke accounting systems in the US, sequential numbers are not required by accountants or auditors. What is required is a demonstration of controls and auditing capability. If an item is deleted, there has to be a trail. The detection of a deleted item is not tracked by a missing number - after all, there are other kinds of tampering to get around requiring sequential numbers, and a demonstration of adequate controls goes far beyond that.

However, I have seen such a requirement in Mexico for reimbursement from the governent. I believe they use it to avoid fraud for multiple submissions to the state agency. IMO, it's not an effective internal control, and has limited scope for catching fraud in the third-party interaction situation.

In general, using an increasing integer id (like IDENTITY) (or GUID - but normal GUIDs are not increasing like that) as a surrogate primary key in tables and clustering on that helps improving SQL Server performance. Note that it is possible for an IDENTITY value to be used up but a transaction to fail or be rolled back, leaving a gap. This gap will not be normally filled in (although it could be by using identity insert).

Here's some COMB ID links:

http://jeffreypalermo.com/blog/use-guid-comb-in-your-database-if-you-need-guid-keys-but-don-t-want-to-take-a-big-performance-hit/

http://www.informit.com/articles/article.aspx?p=25862

We actually modified it a little, used them with CSLA and called them SmartGUIDs. The SmartGUID class exposed a creation date property. Unfortunately, I'm no longer affiliated with that project or company, so I don't have access to the source code to recall our technique exactly.

Kopp

wouldn't using a GUID() with joins in SQL Server be resource intensive with lots of order data? – Mclin

Yes, they take 16 bytes instead of 4. However, in that 16 bytes you get global uniqueness. sqlblogcasts.com/blogs/martinbell/archive/2009/05/25/… We made a special increasing GUID where you could also get a date and time out (effectively your creation timestamp) - so you almost got it for free. – Kopp

Cade brings up a good point--you can use a COMB technique rather than NewSequentialID() to embed a date/time stamp in your GUID, which saves needing a separate column (providing you don't actually need to query this metadata often--extracting it could be a pain). COMB is also a useful technique if you want your business layer to assign the key, since .NET doesn't have a built-in equivalent to NewSequentialID(). – Hyperventilation

Yes, let me track down some links for COMB ID desing and add to the answer. – Kopp

Answer 2

You only need to have sequential ids if it is a valid business requirement.

Ladykiller

...or if your database table's clustered index is on that ID field and the rate of insertion makes merits always inserting at the end of the table. – Hyperventilation

Even if using GUIDs, keys can be generated "sequentially" or not. One is amendable to indexes, the other -- not so much :-) – Regen

Answer 3

Sequential numbers are not necessary, and in some scenarios are a bad idea (in security-conscious systems where guessing is a problem). But it is relatively common to let the RDBMS handle this - most support an IDENTITY auto-incrementing type (although it gets more complex with multiple distributed master servers). Another option is Guid of course - little chance of duplicates.

The prefix approach is quite handy for quickly identifying numbers - but you can choose to just prepend the "OR-" at the layers above the db.

Acrobatic

Answer 4

Accounting and general ledger folks like sequential numbers and if the numbers are missing they want to know why. It is an accounting practice that if it is not done to specs can cost a business time and money.

Subjective

Answer 5

I think we should make a difference between technical ID and what we can call the document ID. There may be legal demands coming into the picture. Here in Sweden, for instance, I think it is required by law that your invoices have invoice numbers in an unbroken sequential order.

However, if I was to design an invoicing system, I would probably choose to have a technical ID that was separate from the invoice ID, preferably with no logic whatsoever (a Guid for instance).

Costume

+1 I don't side with the possible choice of a GUID, but this answer does make a valid point that the invoice ID may be a separate (business) concern from the implementation details. E.g It is then simply a matter of what/where/how [it] is exposed as needed. – Regen

Answer 6

Accounting rules in the country the application will be used might require sequential numbers on invoices.

You users might be used to think in terms of sequential numbers. Maybe the employees place bets on who gets to pack order number 10000? They would probably be quite upset if someone told them that the bets are off, because of the new computer system.

Pallas

Answer 7

If the field in question is your primary key, sequential numbers are faster to insert, even if you skip from 1 to 3, etc.

But sequential numbers do introduce a security issue: people "guessing" URLs, transposing numbers accidentally, or giving away business data (how many users you have, etc.).

One option is to use a GUID (uniqueidentifier type). It's not human-friendly for speaking out loud or typing in, but it is sufficiently random and unique.

If you are using SQL Server 2005, the new SequentialID() function will return a sequential GUID, which gives you both the uniqueness you need, and fast insertion into your table.

If you choose to use some sort of random-ish number, I encourage you to also have a smalldatetime column defaulted to GETDATE() in your table so you can order and filter your rows by the date of creation.

Hyperventilation

wouldn't using a GUID() with joins in SQL Server be resource intensive with lots of order data? – Mclin

A uniqueidentifier type is a 16-byte number, which is 4 times the size of an int-based surrogate key. But in practice, the values are distributed, so "misses" on join comparisons are cheap, and the extra storage requirement is negligible. As a side benefit, uniqueidentifier types can be generated independently on any physical server, so scaling to multiple is a lot easier than with an integer-based key, and scales to more than the 4 billion records that a 32-bit integer offers. – Hyperventilation

Answer 8

You should ask your business contact whether numbers really really really need to be sequential, not us.

And tell that same business person that it is, in most circumstances, as good as impossible for a computer system to give watertight guarantees about this.

Thaumatology