Why I use UUIDs for primary/foreign keys

[eluser]dcheslow[/eluser]
I'm not sure that I'd create a polymorphic relation like that at all. As pointed out in an earlier post, UUIDs *could* be used without any indication of the table they refer to... BUT, retrieving a related record that way would be a nightmare. I use it for my metadata table (createdat, createdby, etc.) because I always JOIN the other way. I've never had a desire to "show me all the records created in the last hour" or anything like that.

When I do want a polymorphic relationship... for example, when doing a Search, I perform a UNION and SELECT the table name... like this:

SELECT
'page' as table,
pageid as id,
title,
....
FROM
...
WHERE
....
UNION
SELECT
'resource' as table,
resourceid as id,
title,
....
FROM
...
WHERE
....

This is no better (or worse) than using integers... except that UUIDs guarantee that I can't accidentally join to the wrong record/table.

I forgot to mention another benefit of using UUIDs in my original post. Well, I mentioned it but did not explain it well. They are disposable.

We all know the edit cycle: fill the fields, validate, if not valid then re-edit otherwise insert/update. This gets tricky when we edit multiple related records... which is a really cool ability using javascript. The reason it gets tricky is that, if we use any sort of autoincrement field, a new record has no ID until it is saved... but the user has not successfully saved the main record yet. We can't save related records until we save the main record because there is no foreign key to relate to. If we use UUIDs as keys, then we can assign an ID to an UNsaved new record and then do whatever we want with related records. If the user does not save the main record, then no harm done. TRUE... this sometimes results in orphaned (related) records, but... From a user's point of view, which as the better system... one that occasionally clears away orphaned records in the background or one that makes the user upload a file AGAIN just because they fat-fingered their phone number? I was really surprised at how many times I used the construct

Code:

if($primarykey == '')

before I switched to UUIDs.

As for UUIDs letting developers be lazy... I'm all for it! All the time I do NOT spend debugging a bad foreign key reference is time I can spend doing something else more productive. UUIDs haven't stopped me from writing bad foreign key references, they just make it easier for me to find them.

Anyway... I'm glad my post made a few people think about UUIDs. Personally, I've done it both ways and (for me) UUIDs are better.

=dave=