News for hackers. Stuff that matters.
Re:New UUID Formats by netghost on Monday June 13, 2022 @10:01AM (#31725699)
Can you give an example of an issue you faced with auto incrementing keys?

Though I can imagine scenarios, the worst I've ever run into was maxing out the integer size, bit that was easily remedied.

Re:New UUID Formats by bruce511 on Monday June 13, 2022 @03:21PM (#31730379)
There are a few, but I'll sumarize;

A) data merging. Merging multiple data sets together with auto-Inc is tricky - especially in the case of related tables.

B) data distribution /replication - especially on and off phones - especially if phones are creating data records offline, then syncing later.

C) parent-child forms. Parent has to be committed to the dB before children can be added.

D) data imports - again especially related data.

I'm not doing justice in such a brief question, each item above deserves a whole exploration.

Re:New UUID Formats by netghost on Tuesday June 14, 2022 @02:11PM (#31743509)
Thanks! Those are a great set of examples.

Perhaps one through-line is that if IDs are not created by a single source, don't rely on auto incrementing IDs.

The parent/child one is interesting. When I've worked with hierarchical data, I tend to wrap the whole process in a transaction, so it may be many commits (by depth), but one transaction.

Re:New UUID Formats by bruce511 on Tuesday June 14, 2022 @10:57PM (#31748632)
>> if IDs are not created by a single source, don't rely on auto incrementing IDs.

Data design outlives programs, and environments by a long time. Circumstances change.

So when I designed the db, there was a single database. But 30 years later we live in a world with smart phones.

Making design decisions because of _current_ circumstances can bite you hard later on.

Reading parent /child. Yes there are ways to mitigate the issue, but it's extra work and code to do so. Ultimately you need the parent id before you can add the children though.

Re:New UUID Formats by dolmen on Tuesday June 14, 2022 @07:23AM (#31737433)
One danger with auto incrementing ids is to be tempted to use the key for sorting instead of using a separate creation timestamp.

At some point you might have to create out of order records (ex: inserting missing data) and that will break the order.