6 ms·
One idea I like is to have a normal internal database ID, but also have a public UUID column to use for URLs, APIs and that sort of thing. This way you can cha
by Jwarder 7y ago
One idea I like is to have a normal internal database ID, but also have a public UUID column to use for URLs, APIs and that sort of thing.
This way you can change the record's UUID at any time to display different data without having to worry about updating a bunch of internal foreign keys. You don't have to worry about a user making easy edits to the UUID in the URL to find nearby records (although you still need real protections if you need to restrict data by user).
- mgamache 7y agoI've used that in the past to retrofit an old database and move away from the id=123 in a URL to a UUID. You can keep all the old relationships triggers ect... Just add an index for the new col... UUIDs are not perfect but for most applications they are good enough (I think)
- yrro 7y agoThere was a story posted recently about how the properties of UUIDs are not a good match for how databases index columns. It proposed the 'ulid' which helps in that regard.
- geezerjay 7y agoThank you for mentioning ulid. Very interesting,and perhaps also useful.
- mywittyname 7y agoI'm curious about this if you remember the reference. My gut reaction is UUID is better because the keys would be more uniformly distributed across the key-space. With sequential IDs, a binary tree would end up always being unbalanced -- though, this effect would be reduced as the database gets larger.
- int_19h 7y agoI recall seeing UUID generators that are specifically tuned for use as keys in databases that specifically support them as primary keys. E.g. this has been around for a while: https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequentialid-transact-sql https://docs.microsoft.com/en-us/sql/t-sql/functions/newsequ...
- dvlsg 7y agoI've used this to good effect before. Note that the endianness may catch you by surprise, though. For example, what's good in postgres may not be good for SQL server.
- wastedhours 7y agoThis is kind of how I've always built applications - the standard ID is viewable to admins, but every public facing utilisation is a random string (with a uniqueness validator). Access to resources is either locked down by an authorisation policy, or sometimes you need open resources (like a public share link), in which case, security through obscurity (with perhaps a secondary string on the record acting as an additional key for non-logged in users).
- _Codemonkeyism 7y agoFunny me too, didn't think others would do the same.
- aaronharnly 7y agoWe do that. Treating the API key as distinct from DB id also helps with loosely coupled microservices, in which the key for a given item may have come from any of several source systems.
- fastball 7y agoWhy not just use the UUID as the ID?
- jon-wood 7y agoUsing a UUID as your primary key can make certain optimisations much harder down the road, most notably sharding which tends to balance really nicely if you do it on an auto-incrementing numeric ID, and less so if you're using random strings because you'll end up with with some of them clustering. They're also much harder to pass around in cases where you need to refer to a specific record, for example if you've got someone in tech support who needs help with a particular object its much easier to say "its record 18434" than it is to try and read out a UUID over the phone.
- dexterdog 7y agoAnd it's much easier for that person to mishear/miskey that ID and work on the wrong record. With a random UUID you will not have that mistake unless you have some seriously huge data. Base64 encode it everywhere and it's 22 characters.
- fastball 7y agoEven with the world's largest dataset, I don't think you will experience an "off-by-one" error with the UUID4 standard.
- perlgeek 7y ago> This way you can change the record's UUID at any time to display different data without having to worry about updating a bunch of internal foreign keys. That is true, but changing the UUID is also likely to break a lot of assumptions that your users make. If your user indexes your forum posts, for instance, and you change the UUIDs, they'll re-index posts with new UUIDs as new posts, and send end users to now invalid UUIDs until the old index entries are evicted.
- aaronharnly 7y agoYeah, I thought that line was weird. It's more like – you can change the record's internal foreign key without breaking your users! Once users have bookmarks, IDs more or less permanent.
- Jwarder 7y agoNaturally it depends on the data. I've used it when we want to show the most recent version of a record on the public website but we want to keep older copies of of the data to reference with past events. Eg customer's current address can always be identified with id x but the address records for specific past orders reference other records.
- athenot 7y agoWhy do you need an internal database ID? I've gone the route of a random number only (similar to a UUID but encoded in a way similar to Youtube video IDs). I used to do the auto-incremented integer but once I got into database sharding, maintaining that global counter started getting a lot more complicated. This lets me have inserts going to several databases without worrying about the counter. (Of course, as with any eventual consistency method, there's a mechanism to ensure no duplicates exist, however unlikely they are.)
- aaronharnly 7y agoThose integer IDs can still be pretty darn efficient for a lot of use cases.
- dexterdog 7y agoAnd inefficient in some others. It all depends on the use case.