I want to present to you a brief and shameful history of how I’ve mishandled SQL lookup tables over the years. I hope it’s useful to anyone wrestling with the issue of massive and proliferating lookup tables.

Years ago, when I first started out with Zend Framework, I took a look at a Magento installation and was horrified by how many SQL tables it required. I didn’t want to have to deal with dozens of tables, and lookup tables were the main thing I thought I could eliminate. Then I had a project that required a couple of dozen fields in a single table, many of which would be empty for many of the rows. This made the issue even more glaring: SQL Lookup tables are useful, but is there a better way to lookup data in PHP? In my hubris I thought there was. Say you have a customer table in your database, and you want to mark each customer with a status. (more…)