Seqential & continuous invoice number in Hibernate / ColdFusion ORM?

Question

One of the requirements of my billing software is that the invoice number must be sequential & continuous. (no invoice number can be skipped).

I tried setting the invoice number as PK, and tried hibernate's increment & native generator, but both of them do not guarantee continuous number when a transaction fails. The next invoice will have the next invoice number, which introduces a gap (not continuous).

What should I do?

Use invoiceID as PK, set invoiceNumber as a Nullable column, and set invoice number at postInsert() with a thread-safe number generator?

Answer 1

Yes, you need to allocate the invoice numbers yourself if you need them to be continuous (i.e. consecutive).

Automatic methods for generating pseudokey values are prone to having gaps, because under ordinary conditions you could have a ROLLBACK or a DELETE and leave a gap.

Trying to compensate by reusing deleted values or renumbering existing records to fill gaps is a terrible idea. It's subject to race conditions and has poor performance in any case.

Generators for pseudokeys guarantee to be unique, but not consecutive.

You could have a pseudokey as the primary key, and also have the invoice number as an attribute that happens to have a UNIQUE constraint on it. This seems redundant but sometimes this is the easiest solution.