Why I use UUIDs for primary/foreign keys

[eluser]wiredesignz[/eluser]
@Jedd, the benefit is that the UUID can never be duplicated.

[eluser]slowgary[/eluser]
sl3dg3hamm3r - how so? Is it because of the date usage in calculating the UUID? I don't get it though because it's a finite number of characters, so the number of unused combinations will have to decrease over time.

[eluser]sl3dg3hamm3r[/eluser]
Well, I confess I am not able to write any mathematical proof for it. But if you imagine a finite range of 1 to 10. Now you generate the first id, the chance of hitting one you didn't use yet is 100%, and now we have a chance of 10% that we generate the same again.
Let's say we used now 9 out of 10 (we are up on 90% that we gonna hit one of the used ones), you have only a chance of 10% of hitting one of the unused ones (only one left), thus leveraging 90% again, to the maximum in fact.
The pool of unused ids decreases with time, thus the chance of hitting one of these gets smaller.

This leads me to my conclusion: the curve flattens.

[eluser]Evil Wizard[/eluser]
What you have to bear in mind if you use MySQL slaves that the SQL commands/queries are run on the slaves and not just the values changed, so if you use UUID() to set a column value, then on the slave it will run UUID() again and generate a different value on the slave, what you need to do is

Code:

SET @x = UUID();
select @x;

using a variable to hold the value of the UUID() allows the value to be inserted without generating a new UUID()

[eluser]slowgary[/eluser]
Ahh. I misunderstand your first post. I thought you were arguing that the possibility of duplication would "flatten", as in drop from 50%. You were merely referring to the "exponential" part. I'm sure you're right. I'm not a math whiz, I'm actually a high school dropout. I also may be mentally handicapped, as pointed out by wiredesignz's earlier post.

[eluser]sl3dg3hamm3r[/eluser]
ah yes, I was referring to the exponential part, or let's say to the curve-developement of the chance which indicates the possibility of hitting an used one. The closer to 100%, the flatter the curve gets.

[quote author="slowgary" date="1244490795"]I also may be mentally handicapped, as pointed out by wiredesignz's earlier post.[/quote]

Wouldn't take it neither serious nor personal.

[eluser]slowgary[/eluser]
Neither would I ;-P

[eluser]jedd[/eluser]
I'm not sure how you get from this:
[quote author="jedd" date="1244489202"]Uniqueness (or degrees of same) isn't my concern - my problem remains that I can't understand the asserted benefits of having a unique UUID as a key across all your tables. I can grok the benefits if it's across two very similar databases that you may one day want to merge, but that has to be an edge case for most people.

The earlier suggestion was for logging activity within the db - something that I'm going to do on my project at some point - but AFAICT you either have to:
a) search through all tables looking for the UUID (this seems expensive) or
b) record the table with the UUID (this seems functionally equivalent to auto-inc ID + table name).[/quote]

To this:
[quote author="wiredesignz" date="1244489967"]@Jedd, the benefit is that the UUID can never be duplicated.[/quote]

I'm having the sensation of having previously experienced deja vu.

[eluser]n0xie[/eluser]
I've already mentioned our reasons for using integers, but I would like to point out the discussion on Jeff Atwood's blog. I don't know how many of you follow his blog, but the responses might be of interest to some of you.

[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=