I have an honest question. Why would anybody, ever, make an order number that only has 5 digits. Even if it's just a home-hobby project, the cost of changing to 7-10 digits is so small a d negligible that I can't see any reason for choosing anything lower. Like the 640k that was once "enough for the long term future" in DOS. I understand that hindsight is 2020, but can't wrap my head around not starting bigger when there's no extra cost (nearly).

Could you shed some light on that?

Since 10 digits puts us in billions, you're really asking "why would someone do 'CREATE TABLE ... id INT AUTO_INCREMENT' when they could use a BIGINT?" These days, there's rarely a reason not to use a BIGINT, but I also have a little trouble faulting someone for thinking 2B would be enough when they're currently at 10k.

Please don't use BigInt. It is the same size (usually 16 bytes) as GUID/UUID in binary but has lots of drawbacks and no real upside other than they are easier to type in when they are still small.

1) The ids are clustered around the starting point so an invalid join will return data when it shouldn't. MIN_ID exists for almost every table/object type. With a well designed ID this shouldn't return any data.

2) BigInts are different sizes on different platforms. I've worked at a place 1 order of magnitude (base2) from overflowing JS Number and breaking most client code. This was due to bug that consuming a Seq ids quickly but there was no going back smaller.

3) Will often be coded client side as a Int and no one will notice until you grow large enough to overflow INT

Really, just avoid any sequential, numeric IDs and you will be good, IMO.

> Really, just avoid any sequential, numeric IDs and you will be good, IMO.

Sequential ids make better as database indexes. Do you propose using both a uuid for external purposes and a sequential id as the primary key?

Or something like uuid v6? http://gh.peabody.io/uuidv6/ (which isn't widely supported)

Postgres and SQL Server both have 8 byte BigInts. Is there a database that has bigger ones? I haven't heard of it.

Bigint is a 64-but (8 byte) signed integer. It’s half the size of a UUID and using a sequence rather than random ids leads to significantly less WAL ok Postgres when full page writes are enabled.

> Could you shed some light on that?

First off, I had a brainlapse, their order numbers were 6 digits. I'm not entirely sure what they max was in their system, it was just that's where they were at in the series.

And they didn't have that many orders per year, less than 10k, as one order could be for say five containers of goods. So it was not like they'd exceed 9 digits in the foreseeable future.

Or so we thought...

Obvious answer: because they started at 1 and have had less than 100,000 orders.

I know of a company that had enough trouble with vendors after their PO numbers rolled over that they modified their EDI transmissions to add 1,000,000,000 to every order number.

Back in the day, we were taught to use database constraints to validate user inputs.

Memory, storage and compute were also more limited, so there was an extra cost to over-spec.

Isn't this still the best practice? I do it to prevent the possibility of invalid data.

Just had a similar project. Spent loads of time replacing %d with %s in printf and \d to \w in regex. Tedious! All from design choices in 2013.