2

My table currently has a boolean column, let's call it the "important" column, reflecting whether a record is urgent or not. I want to transition to using a varchar column that reflects a record's urgency as one of a few possible values, like "normal", "urgent", "extreme". The column name needs to change to, so I'm planning on doing something like this: create a new column called "urgency" to hold the normal/urgent/extreme value, and selectively migrate the existing boolean date into the new column.

Is this a good approach, and are there any gotchas I should watch out for?

1

In MySQL, the BOOLEAN data type does not really exist. If you create something like this:

mysql> CREATE TABLE imp (id int PRIMARY KEY auto_increment, important BOOLEAN);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO imp (important) VALUES (0), (0), (0), (1), (0), (1), (1), (1), (0), (0), (0), (1);
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

You will see that BOOLEAN is just an alias for tinyint(1):

mysql> SHOW CREATE TABLE imp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                      |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| imp   | CREATE TABLE `imp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `important` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

And you can even insert a 2 value:

mysql> INSERT INTO imp (important) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM imp WHERE important = 2;
+----+-----------+
| id | important |
+----+-----------+
| 13 |         2 |
+----+-----------+
1 row in set (0.00 sec)

The less traumatic way, but more secure and flexible that I can think is, instead of creating an enum or a string, reuse the column and set a foreign key if you are using InnoDB:

mysql> CREATE TABLE levels (id tinyint primary key, name varchar(30));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into levels values (0, 'normal'), (1, 'urgent'), (2, 'extreme');  Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE imp ADD CONSTRAINT important FOREIGN KEY (important) REFERENCES levels(id);
Query OK, 13 rows affected (0.02 sec)
Records: 13  Duplicates: 0  Warnings: 0

That way you cannot insert values that do not exist on the levels table and you can add values later very easily (also, you do not have to rebuild the original table).

  • That looks like a cool solution. I'm working in the context of a Rails app though, and I'm inclined to keep the design database agnostic. I'm guessing that while a string (varchar) column isn't the most elegant solution, it would be the most portable? – ivan Mar 17 '14 at 3:35
  • You can apply a similar concept by creating a Model called levels, even if it is a string (I am assuming you are using ActiveRecord). If it is needed or not, depends on how important is consistency at db and how likely is that you will create new levels (but at the very least, you want to check your inputs in the Model). – jynus Mar 17 '14 at 13:37
0

This approach should work. However, I would personally use ENUM or TINYINT as the datatype for the urgency column, it takes less space in the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.