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

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.

Answer 2

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).