Using Numbers in a Database...

As I was checking some code, I though that I should write a note about good and bad practices.

When you create a table entry and want to use a form of status (or whatever other type of multi-state entry with a fairly small selection such as the sex of a person) you often think of using an enumeration or at least an integer. For a status, you could use 0 meaning off and 1 meaning on. Maybe you have a third status: 2, meaning it is on and shows a form to the user.

In general, this works as a great optimization. If you use a string such as "on", "off", "with-form", it ends up being much longer to handle than an integer (think of the network transfers, use of strcmp() instead of switch(), storage space, etc.)

However, there is always one concern here: if that number is saved in your database, then it becomes unchangeable. So "off" must always be represented as zero (0). This is a drawback and there isn't any good way in C++ to prevent such changes from occuring.

Yet, to avoid problems, I suggest that you avoid using enumerations for numbers that are to be saved in a database. For example, you could have the following enumeration:

enum status_type {
  status_type_off,
  status_type_on,
  status_type_on_with_form
};

Up to here, no problem. One day you decide to add another status and you update your enumeration as follow:

enum status_type {
  status_type_disabled,
  status_type_off,
  status_type_on,
  status_type_on_with_form
};

So you added a new state called disabled. This compiles, absolutely no problem here! However, as you may notice, the value of status_type_disabled is zero (0) and the old status_type_off enumeration that was zero (0) is not one (1). This means your new enumeration is not compatible with the data previously saved in your database.

To avoid this problem, I strongly advice for the use of static constant instead. So you first define a typedef and then define the different values making sure they have different numbers.

typedef int status_type;
static const status_type status_type_disabled = -1;
static const status_type status_type_off = 0;
static const status_type status_type_on = 1;
static const status_type status_type_off = 2;

These values can then be documented with a BIG WARNING so as to make sure people see it (but they won't...) that says those values are saved in databases and thus they cannot be changed.

Syndicate content

Snap! Websites
An Open Source CMS System in C++

Contact Us Directly