- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Extra WTF: why is the UUID column a varchar 128? Uuids are 128 bits. They're not variable, and not that long, so use a char 32 already.
Admin
"First, users should never see an auto-incrementing ID. That means you need to use UUIDs. But UUIDs are large and expensive, so they should never be your primary key, use an auto-incrementing ID for that.
This is not, in and of itself, a radical or ridiculous statement"
Is this really not a radical or ridiculous statement? Maybe I'm old fashioned but It would certainly raise concerns for me. surely you want to have one primary key field for each object?
Admin
It makes some sense from a security standpoint if you do your API right.
Basically imagine your standard MVC layout. Your frontend only ever sees UIDs and all its queries toward the backend only use UIDs. The backend and DB all use the numeric ID for all actual DB queries. This creates a complete separation where the two systems are entirely decoupled while still giving you all the advantage of using numeric IDs for actual processing.
Edit Admin
@RLB: I would hope by now most SQL-ish databases have a native UUID datatype. If there is a native UUID datatype, then storing your UUID value in the database as anything other than that datatype is nuts. ISTM a 128-bit integer datatype is the least bad next choice if the database supports that. Then a 16-byte binary string, and only finally some length 32 Unicode character hexadecimal display format.
Admin
I need to think about what you said, but I find it really hard to believe that whatever benefit you get from that separation outweighs the downsides. For starters, if not done very carefully right it would turn any debugging session into a nightmare.
================================================================================================== "But UUIDs are large and expensive, so they should never be your primary key, use an auto-incrementing ID for that." The fact that they aren't the PK wouldn't make these UUIDs smaller or less expensive. If Bjørn has already commited himself to including them in the table, adding another (integer) field to be the PK would only make things worse
Edit Admin
The problem with using auto-increment as PK is that then you are using it for FK. If you need to insert 100,000 records AND their attendant child records, you can't pre-generate the PK/FK and insert them all at once. You can insert the primary records, re-fetch them with their UUIDs, and then put the auto-increments into the child records. I'm just not sure if the performance value of auto-increments is worth that extra mess.
Edit Admin
The UUID is not a primary key, it's an external identifier. It does happen to also uniquely identify a record, but it doesn't fill the role of primary key in the database.
Edit Admin
Pretty sure I've been doing that for years in my .NET applications. [looks at code] Yep.
Edit Admin
No, use the UUID type in Postgres (which they were using). 16-byte storage. Not 32.