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

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

Answer 2

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.

Answer 3

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.