I would strongly say strings are actually the _only_ type you should use for IDs. Prevents the vast majority of buggy client behaviour and gives you good flexibility to change how you do things over time.
For us, a big part of it was usability with cursor selection etc - in addition to it being immediately obvious what the ID was for.
Once we finally had that rolled out everywhere, we ended up collecting up every other ID we'd ever used and mapped it to its KSUID resource equivalent, so now all our IDs work standalone without type/context info, even across environments (and thankfully we'd never had any collisions on the old IDs)
---
Going back to the typing - the most difficult part of migrating our IDs actually was converting them all to string types. With Postgres this is a little slow but ultimately fine, but with Mongo you have to actually remove and reinsert every document - you cannot (or at least could not) update IDs in place.
Strings have their own issues. One place I worked had a bug where users could take over accounts because of missing/inconsistent unicode canonicalization. Case can be a problem, as can special characters.
There's something to be said for strings, though. Prefixed IDs that mark the type can be nice to work with when it's an otherwise opaque ID, but they're a pain to handle internally.
If you're storing third-party IDs, you probably want strings...unless their clever JSON API returns 1.3E6 as a number. Or the string "null;" that's always an adventure.
IDs should really be their own type: string operations don’t really make sense on them. If you use strings, there should probably be a constraint on the acceptable characters and lengths: e.g. only digits, exactly 10 or something like that.
I personally like the idea of using urls using a domain you control + a path that reflects the type of data it’s an ID for.
If the main benefit of prefixed IDs is debugging then a dev tool can be created that would just look up the ID in all tables and report what object it corresponds to.
I would strongly say strings are actually the _only_ type you should use for IDs.
Just got that in the face at work. In a large custom integration we read orders, they have unique order numbers, nice 5 digit things. So in the database, they became integer primary keys, with lots of child tables.
Fast forward 5 years, customer switches ERP system and they ask "hey, the order numbers, you do support 10+ digits right?"
Changing the database is relatively easy, changing the code is a chore, but worst part will be going over all the queries and their parameters, especially in the reports.
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).
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.
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.
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.
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.
Comments
I would strongly say strings are actually the _only_ type you should use for IDs. Prevents the vast majority of buggy client behaviour and gives you good flexibility to change how you do things over time.
---
My company ended up with a simple KSUID implementation of our own - https://www.cuvva.com/product-updates/showing-off-our-fancy-... (having originally used UUIDs and Mongo ObjectIDs)
For us, a big part of it was usability with cursor selection etc - in addition to it being immediately obvious what the ID was for.
Once we finally had that rolled out everywhere, we ended up collecting up every other ID we'd ever used and mapped it to its KSUID resource equivalent, so now all our IDs work standalone without type/context info, even across environments (and thankfully we'd never had any collisions on the old IDs)
---
Going back to the typing - the most difficult part of migrating our IDs actually was converting them all to string types. With Postgres this is a little slow but ultimately fine, but with Mongo you have to actually remove and reinsert every document - you cannot (or at least could not) update IDs in place.
Strings have their own issues. One place I worked had a bug where users could take over accounts because of missing/inconsistent unicode canonicalization. Case can be a problem, as can special characters.
There's something to be said for strings, though. Prefixed IDs that mark the type can be nice to work with when it's an otherwise opaque ID, but they're a pain to handle internally.
If you're storing third-party IDs, you probably want strings...unless their clever JSON API returns 1.3E6 as a number. Or the string "null;" that's always an adventure.
IDs should really be their own type: string operations don’t really make sense on them. If you use strings, there should probably be a constraint on the acceptable characters and lengths: e.g. only digits, exactly 10 or something like that.
I personally like the idea of using urls using a domain you control + a path that reflects the type of data it’s an ID for.
To be fair, string operations don't usually make much sense in general.
azure does this for resource IDs and its one of the few things i really enjoy about the platform.
If the main benefit of prefixed IDs is debugging then a dev tool can be created that would just look up the ID in all tables and report what object it corresponds to.
Just got that in the face at work. In a large custom integration we read orders, they have unique order numbers, nice 5 digit things. So in the database, they became integer primary keys, with lots of child tables.
Fast forward 5 years, customer switches ERP system and they ask "hey, the order numbers, you do support 10+ digits right?"
Changing the database is relatively easy, changing the code is a chore, but worst part will be going over all the queries and their parameters, especially in the reports.
So yeah, lesson learned.
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.
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.
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.