Why I use UUIDs for primary/foreign keys

[eluser]jedd[/eluser]
Hi Dave,

An interesting thread - thank you.

A very competent programmer friend of mine also advocates the use of GUIDs everywhere. I'm not yet convinced, though, as they seem to require a bit more effort, make some things more complicated, and I don't see compelling advantages to offset that extra pain.

Another reason you might want to use UUIDs is that, because they are strings, they are usable as file/folder names and javascript ids.

Of course, tablename/id - is also unique for folder/filename use.

While I concur that for most of us, we should let the performance and storage penalty of working with GUIDs rather than integers, I'm also wary of fully adopting the 'let the hardware deal with my inability to optimise' approach. I don't think it's necessarily inelegant, but it does seem to be a lazy way of thinking.

I'm, personally, against making URLs too editable/readable.

I guess with a lot of this (web) stuff (programming) there's the expectation that the debugging phase of the project will last the entire lifetime of the project, so it's very handy to be able to edit the URL's forever, and expect some sanity and predictability while doing so.

I think if you're relying on a GUID's obfuscation for your security, then you're missing the point of security.



I also think any obfuscation should not replace a genuine security model - viz:

A naughty person might be much more tempted to hack /user/123 than /user/BAE7DF4-DDF-3RG-5TY3E3RF456AS10.

Further, I think that any obfuscation of user ID's should be done closer to the view, than the model, and not just for debugging reasons, but it just seems to be a neater solution to that problem.

Collisions are very (actually... very very) unlikely with a number this large...

I grant you that collisions with these are unlikely. However, SERIAL types in MySQL, for example, are one better - they are impossible. While your UNIQUE attribute of your PK will protect you, it means you need additional code wrapped around the INSERT to ensure this, even if 'just in case', and that's code that an auto-increment approach doesn't need.

We shouldn't hard code primary key values anywhere, so making primary keys readable doesn't impact coding at all.

I accept this claim, also, but suggest that it's common to want to type something in during debugging (again, not just during the development stage of the project) and it's much easier to play with users 5, 6, 7 and 8, than BAE7DF4-DDF-3RG-5TY3E3RF456AS10, BAE7DF4-DDF-ERG-5TY3E3RF456AS10, BAE7DF4-DDF-3RG-51Y3E3RF456AS10 and BAE7DF4-DFD-3RG-5TY3E3RF456AS10.

As for library developers only targeting the majority audience... well, if we all coded to the most common browser, then we'd all be using IE <perish the thought>.

OTOH, we'd all be coding PHP for Apache .. so it's a tricky call to make there.

gtech raises another significant benefit. He (and I) maintain metadata separately about rows inserted/edited/deleted. I used to have insertedat, insertedby, etc. in every table... but UUIDs make it even simpler. I simply keep one table called 'metadata' ...

I've been thinking about how to log changes to my DB, given the huge number of tables that this thing will be managing, and ended up (probably very logically) at this approach. However, I just assumed it'd be something like: ACTIVITY_LOG ( id SERIAL , table_name CHAR (60) , activity CHAR(300)) - or similar. In other words, I'm not convinced that tracking back a UUID - which would mean having to find which table that refers to - is easier than my approach here. Again, as cited elsewhere in this thread, there are significant advantages to being able to look at the ID and gain some insight into the relative age of that record.