Sequential order numbers, and why random ones cost you
Random order references collide, cannot be sanity checked, and quietly reveal nothing. A database sequence fixes all three.
The case against random
Random references get chosen because they hide volume and feel collision safe. In practice they cause problems:
- They can collide, and the handling for that is rarely tested
- Nobody can tell whether one is plausible, so typos become support tickets
- They carry no ordering, so anything sorted by reference is meaningless
- Customers reading them aloud make mistakes, because there is no structure to anchor on
The volume argument is weaker than it looks. Start the sequence somewhere other than one if you would rather not advertise being new.
Use the database
A database sequence is the correct tool. It is atomic under concurrency, it never repeats, and it does not require your application to read the last value and add one, which is a race condition waiting for a busy afternoon.
CREATE SEQUENCE order_number_seq START WITH 1000 INCREMENT BY 1;
-- when creating an order
SELECT 'ORD-' || nextval('order_number_seq');A short prefix helps humans and search. It makes a reference recognisable in an inbox and stops it colliding with unrelated numbers in your own systems.
Migrating from random
Do not renumber history. Old references exist in emails, invoices and customer records, and changing them breaks every one of those. Start the sequence above your highest existing value and let the two coexist.
Sequence, not primary key
Keep the customer facing reference separate from the internal identifier. They serve different purposes and have different constraints. The reference is for humans and can change format; the key is for joins and must never move.
The point
Reference numbers are an interface. They get read over the phone, typed into a search box, pasted into a payment description and quoted in a complaint. Optimise them for the person reading them back to you, because that is where the cost actually lands.
Need help with any of this?
These notes are free and always will be. If you would rather someone just set it up, or you are stuck on something similar, get in touch at hello@opsira.io.