James Sapara’s Personal Blog

In my line of work, I see a lot of legacy databases. We often tasked with converting them to something more modern. My #1 gripe with some of our junior programs in this task is not converting fields to a more appropriate data type when this takes place. Two examples that I come across often.

1. The boolean as anything but a boolean

This happens frequently when people are moving from an access database to anything else. Access wasn’t exactly friendly with boolean values (thou, it was possible to make a boolean field as an int(1) ). At any rate, most people who designed access databases used a char(1) where the values where either ‘Y’ or ‘N’. It’s an obvious mapping to a boolean. For whatever reason, people just leave these fields as the single character fields, instead of migrating it to a more friendly boolean field. So, that’s the first no-no of database design. Using anything but a boolean (or I’ll accept a bit field) for boolean values

2. The complete avoidance of enumeration fields

There are many cases when a field represents something like the status of an order: new, pending_payment, processed, shipped, complete. Often, I see these sorts of status as varchar fields. This is fine and dandy, except I have no idea what are acceptable values to put in the that column. I could do a select unique on it and hope that there is atleast one record representing each possible value, but that isn’t a reliable way to do it. One could argue the DBA would have documentation on what should go in there, or that the business logic would dictate it; but, most projects skip those points. Really, only the orgional programmer has any hope of remembering what can go in those fields, making them somewhat problematic to work with for future work.

In comes enumerations. Because you specify what the heck is valid in the field, you don’t have to guess what can possibly go in them. It’s actually their intended use. It also provides a form of feedback when you try to set that field to something your not expecting. Further preventing people from doing stuff like “New” vs “new” (which I have seen…).