Thursday 21 June 2007

Identity or UniqueIdentifier (GUID) for Database Key?

Jeff Atwood posted an interesting article on whether to use GUIDs or integers for primary keys in databases. Its been a while since I did any database design, and this issue has come up in a new ASP.NET project I have recently begun.

I'm not particularly bothered about the disk space / performance issues. I suspect the difference is not all that great and in any case, my database is not likely to be huge.

As I see it the reasons for me to use GUIDs are:

1) The auto-generated ASP.NET 2 user database already uses uniqueidentifier, thus my instinct is to use it in all my tables as well for consistency.

2) Easier to copy records to other tables / import into other databases without breaking relationships. (however, there appears to be a SET IDENTITY_INSERT command in SQL Server which means that this is also possible with IDENTITY fields)

There is however one significant drawback, which may move me back to using integers. The issue is that of URLs. I want users of my site to be able to generate links to individual records in the format:

With a GUID, this becomes a lot more unwieldy and much more likely to encounter problems with line-wrapping truncating the URL. (yuck!)

I did consider adding another integer column alongside the GUID which would act as the URL key, but this seems ridiculous to have two keys in the table.

Some people however have complained that IDs should never be present in a URL, as it allows people to randomly choose other IDs and perhaps get to pages they shouldn't. For my purposes this isn't a big deal. I have a 'private' field in my database that I will be checking before displaying an item to a user.

Constructing a URL out of an items textual description can make for nicer URLs in some cases:

But this introduces an artificial constraint on the description being unique (e.g. why can't I have two blog entries with the same title?), and also can result in messy URLs if the description is long and contains punctuation or unusual characters.

So it looks like I may be heading back to integers, unless someone has a clever idea of how to make friendly URLs when the key is a GUID.

1 comment:

tayfun said...

MySQL also provides shorter GUIDs, or Universal Unique ID as the term used by them.

What you can do is use both a shorter ID and an understandable item name etc. to have nice looking URLs.