Graham Weldon

Ditching enum for something more portable and maintainable with CakePHP

Friday, 27 March 2009
Tagged: Website, General

It's one of those questions that I see more and more these days in the CakePHP IRC Channel: "Why doesn't schema support enum?". The reason is that the portability of the enum storage type is not complete. In the interest of keeping portability at a maximum, enum is not supported for schemas.

So what now? Well, fortunately there is not only a solution to the problem, there is a method of achieving an enum style storage type using simple tables, and getting a more maintainable set of data in the process. I'll explain how:

Instead of storing your data as an enum type, you can utilise the 'belongsTo' association type, and link the entry to another table. Consider a user account model, where you would have a user_type enum('active', 'banned', 'deleted'). This can easily be stored in a UserType model:

CREATE TABLE `user_types` ( `id` CHAR(36) NOT NULL, `name` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`) );

This allows you to very easily add additional types later as your needs change, rather than making database modifications to support the requirements changing.

Ensure your `users` table has a reference to the user_types table:

CREATE TABLE `users` ( `id` CHAR(36) NOT NULL, `user_type_id` CHAR(36) NOT NULL, `username` VARCHAR(45) NOT NULL, `password` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) );

Set the User model association with UserType:

<php class User extends AppModel { var $belongsTo = array( 'UserType'); }>

Checks can be performed against the string name of the UserType after fetching the User data:

$user = $this->User->find('first', array('conditions' => array('username' => 'dude'))); echo $user['UserType']['name'];

Hopefully this helps someone out there.