[simonw]

The one time I used UUIDs as primary keys I quickly regretted it. They're huge, they're not possible to manually enter ("which row ID was causing that bug?") and they make foreign key relationships ugly and large too.

If you want to generate IDs independently of the database you can do so using a "ID generator" mechanism.

Set up three redis instances (or MySQL or anything else that can increment a counter). Have each one increment by three each time. Start then at 0, 1 and 2.

Now you can ask any of those theee instances for a new ID and you'll get one that has not been used before, thanks to them being offset from each other.

I first saw this technique used by Flickr when they switched to a Shaffer database.

[gldalmaso]

I believe one of the sought after features when people want to generate ids outside the database is for offline apps, so there is no way to connect to outside counters. The client should have a way to locally generate an id that is final and will be used for the rest of the entity's life. This simplifies the synchronization with the backend because otherwise there must be a (temporary) client id and a final true id. This leads to very convoluted code that deals with two logically different entities, the "pending" ones and the "synced" ones.

[parvenu74]

UUID (or GUID on SqlServer) are essentially 128-bit integers. The fact they don't have inherent meaning beyond uniquely identifying a row and establishing referential integrity is a plus. In my opinion the primary key is for the database's needs; a human-friendly number or code for lookup is both optional and trivial to implement so when there's a requirement for an easy reference number or code for a record I use both a GUID for the primary key and a unique (or autonumber) field for human reference.

[firebacon]

Is "Shaffer" a typo of "sharded"? If not I'm curious what the term means (all that google turns up are a bunch of papers by a guy with that name)?