PaulMarshall

CakePHP and UUIDs

Posted by Paul Marshall on Sun, Feb 13 2011 17:02:00

A UUID (Universally Unique Identifier) is a 36 character string comprised of 32 hexadecimal digits separated by 4 hyphens. UUIDs are typically used for primary keys when the key needs to remain unique across many tables, databases, or systems. This is often the case with database replication. UUIDs are designed to be "practically unique". You could generate 1 billion UUIDs every second for 100 years before your probability of generating a duplicate would be 50%.

CakePHP Support

CakePHP supports UUIDs and will automatically generate them for primary keys with the data type CHAR(36) or BINARY(36). In addition you can generate UUIDs in your app using String::uuid().

Why Use UUIDs?

Abstract Data

UUIDs can be used to provide some powerful data abstraction and interesting queries. In ActionTracks, I store additional attribute data for contacts, profiles, and users in one table. A field in the data table is called entity_id and stores the id of contacts, profiles and users. Using integer based primary keys would require generating the keys from a central source. This would be impossible using auto incrementing integer based primary keys.

Advanced Database Features

If you plan to use advance database techniques such as replication, partitioning, or clustering you will probably need unique primary keys. This is one of the most common reasons to use UUIDs. If you plan to use these features or think that your application may grow to the point of needing multiple database servers then you should consider using UUIDs from the beginning to avoid rekeying your application.

Obfuscation

UUIDs can help to obfuscate your application. While this is certainly not foolproof, identifying a 36 character string is a lot harder than identifying an integer.

Disadvantages

Unfortunately, the power of UUIDs comes at a price. Using UUIDs can have some performance implications especially when using InnoDB. A UUID is typically much larger than an incremental id. In many cases, the id is larger than the data it represents. In InnoDB, secondary keys always include the primary key, This can result in significantly larger indexes.

Tips

Although CakePHP does not support the 16 byte hex encoded UUID with the key type of BINARY(16), it does support BINARY(36) which is still better than using CHAR(36) which can be slowed down by collation.

It is sometimes useful to add a UUID generator to your site for initial development and testing.

1 Comment

Wendy said on Mar 29th 2011, 00:03
Thanks for this article. It really helped in my database planning phase that I'm in for a current project.
Add New Comment