Re:Auto increment is a terrible idea
by rkagerer on Thursday November 30, 2017 @11:04AM
(#15816776)
Your problem isn't auto-increment, it's what you're deciding to expose outside your database.
Many of your critiques stem from the simple assumption that primary keys will be shared externally (see final link below on why that's not necessarily a good idea). There's nothing wrong with using auto-increment primary keys, then creating a different, dedicated field to store external identifiers. Before UUID's were invented we used simple random numbers for the latter (converting to hex or base-64 in URL's if more compactness was desired) and they worked just fine.
I agree 100% with frik's comments. Keep in mind your team will be dealing with primary keys a LOT. In our own projects I've seen developers become a little less efficient when working with databases that overuse bulky UUID's. I don't feel they're particularly "human friendly". They take up more pixel width in SQL query results, leading to more back and forth scrolling. JOIN results starting with a whole screenfull of ID garble weren't uncommon. Unless your developers have eidetic memory they wind up doing more copy and pasting (with the associated cursor movements, clicks and ergonomic odometer ticks) to jot them down or share them. Ever tried to name a UUID over the phone? In the early days of your project, when you're doing a whole heck of a lot of raw interaction with your database, auto-increments can offer a slight advantage of being so much more compact, easier to remember, and simple to communicate. It may not sound like much of an edge, but it's one less friction point slowing down collaboration.
I think you might also be overlooking the fact that some UUID implementations are designed to be unique, not random, and depending on which you use might leak information or be predictable (https://blogs.msdn.microsoft.com/oldnewthing/20120523-00/?p=...). I'd be careful about sharing those primary keys unless you're sure they're generated from a decent [maybe cryptographically secure pseudo-] random number generator and aren't derived from the current time / MAC address. If you want to dive deeper there's a subtle tradeoff here between privacy and ease of sorting (e.g. in a clustered index).
I empathize with your argument that sequential keys can lend themselves to typos deleting the wrong record (which can be a pain even in non-production environments) and have seen arguments to the benefit of knowing UUID's in advance before a record is inserted (less constraints writing your SQL, reduced roundtrips) and ease of merging data between databases. It can also be deadly if your auto-increment generator reuses deleted keys and your system doesn't account for that. In the past I've created my own ID dispensing mechanisms optimized according to the features I wanted (e.g. sequential or random, reservable, globally or locally unique, human-friendly with number of digits growing in chunks, etc) but admittedly that could be overkill for your project.
I don't have anything personal against UUID's and don't mean to come off condescending, I just think you missed some aspects in the message you broadcast.
Many of your critiques stem from the simple assumption that primary keys will be shared externally (see final link below on why that's not necessarily a good idea). There's nothing wrong with using auto-increment primary keys, then creating a different, dedicated field to store external identifiers. Before UUID's were invented we used simple random numbers for the latter (converting to hex or base-64 in URL's if more compactness was desired) and they worked just fine.
I agree 100% with frik's comments. Keep in mind your team will be dealing with primary keys a LOT. In our own projects I've seen developers become a little less efficient when working with databases that overuse bulky UUID's. I don't feel they're particularly "human friendly". They take up more pixel width in SQL query results, leading to more back and forth scrolling. JOIN results starting with a whole screenfull of ID garble weren't uncommon. Unless your developers have eidetic memory they wind up doing more copy and pasting (with the associated cursor movements, clicks and ergonomic odometer ticks) to jot them down or share them. Ever tried to name a UUID over the phone? In the early days of your project, when you're doing a whole heck of a lot of raw interaction with your database, auto-increments can offer a slight advantage of being so much more compact, easier to remember, and simple to communicate. It may not sound like much of an edge, but it's one less friction point slowing down collaboration.
I think you might also be overlooking the fact that some UUID implementations are designed to be unique, not random, and depending on which you use might leak information or be predictable (https://blogs.msdn.microsoft.com/oldnewthing/20120523-00/?p=...). I'd be careful about sharing those primary keys unless you're sure they're generated from a decent [maybe cryptographically secure pseudo-] random number generator and aren't derived from the current time / MAC address. If you want to dive deeper there's a subtle tradeoff here between privacy and ease of sorting (e.g. in a clustered index).
I empathize with your argument that sequential keys can lend themselves to typos deleting the wrong record (which can be a pain even in non-production environments) and have seen arguments to the benefit of knowing UUID's in advance before a record is inserted (less constraints writing your SQL, reduced roundtrips) and ease of merging data between databases. It can also be deadly if your auto-increment generator reuses deleted keys and your system doesn't account for that. In the past I've created my own ID dispensing mechanisms optimized according to the features I wanted (e.g. sequential or random, reservable, globally or locally unique, human-friendly with number of digits growing in chunks, etc) but admittedly that could be overkill for your project.
I don't have anything personal against UUID's and don't mean to come off condescending, I just think you missed some aspects in the message you broadcast.
Here's a great, easy-to-read analysis of the pros/cons and when to use each: https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-ca...