CODEWITHSUNDEEP
mysqlruby-on-railsdatabase-design
B Foxx
B Foxx

Reputation: 35

Is there anything wrong with storing constant reference data as single char instead of ints in a database column?

I'm currently building a Rails app, and I'm trying to make sure I don't make any big mistakes now in the database design (MySQL) that will haunt me down the road.

Currently I have a bunch of fields in various tables that reference simple constant data. For example, one of my tables is called rates and it's fields are id, amount and unit, with unit being per hour / day / week / month.

These values will not change, and instead of using a reference table I just used a single CHAR to represent the values, so hour would be 'h', day would be 'd', week would be 'w' and so on. I figured this would make the database more human friendly and limit the possibility of IDs changing somehow and all the data being corrupted because of it.

Does this seem like a reasonable approach, or am I missing some potential pitfall?

Upvotes: 2

Views: 195

Answers (2)

Mike Purcell
Mike Purcell

Reputation: 19989

If it were up to me I would use a lookup table which contains key/value pairs, where the value is anything you want it to be. Something like this:

CREATE TABLE `lookup_rate_type` 
(
 `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, 
 `name` VARCHAR(20) NOT NULL, 
 PRIMARY KEY (`id`), UNIQUE KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then, in any tables where you need to reference a rate, simply use the id which correlates to the rate you are interested in, lets add a few rows:

INSERT INTO `lookup_rate_type` SET name = 'second'; # id = 1
INSERT INTO `lookup_rate_type` SET name = 'minute'; # id = 2
INSERT INTO `lookup_rate_type` SET name = 'hour'; # id = 3

Now, if you have a table which needs a reference to rate you would this:

INSERT INTO `some_table` SET rate = 27, rate_type_id = 3; # 27 / hour
INSERT INTO `some_table` SET rate = 3600, rate_type_id = 2; # 3600 / minute

The nice thing about this approach, if you ever decide you want to use just the first letter to identify a rate_type, you simply need to update the lookup_rate_type table:

UPDATE `lookup_rate_type` SET name = 's' WHERE ID = 1 LIMIT 1;

Even though you changed the name value (within a context you understand, second became s), any tables storing a relation to the value, will remain unchanged.

With this solution you can add as many rows as you need to the lookup table, vs having to run an alter statement just to add an enum, which can be painful if dealing with a large table.

The only drawback with this solution is you will probably need to use class constants to allow your application code access to the int values so you can conduct CRUD ops according to your business logic, something like:

class LookupRateType
{
    const SECOND = 1;
    const MINUTE = 2;
}

// Calling code example
$sql = sprintf('INSERT INTO some_table SET rate = %d, rate_type_id = %d', 27, LookupRateType::MINUTE);

Also, if you wanted to, when dealing with a small list of constants you can forgo the lookup table altogether and deal only with class constants. The drawback here is you will have to know the id when looking for specific data, vs using a query:

# If you use class constants only, you can do this
SELECT * FROM some_table WHERE rate_type_id = (SELECT id FROM lookup_rate_type WHERE name = 'hour');

# But if you can lookup the context with the code you can simply input the id
SELECT * FROM some_table WHERE rate_type_id = 3;

These are scenarios I have come across in dealing with a multitude of different applications, and have found the lookup table and/or class constants to be the best solution.

Upvotes: 1

Ilmari Karonen
Ilmari Karonen

Reputation: 50338

If you really want to do this, I'd suggest using an ENUM column:

CREATE TABLE rates (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  amount INTEGER NOT NULL,
  unit ENUM( 'hour', 'day', 'week', 'month' ) NOT NULL
);

This is even more readable than using single-character abbreviations, while being no less efficient. In strict SQL mode, it also ensures that attempts to insert invalid values will be reported as errors.

(However, eggyal's suggestion of using a SMALLINT column and measuring durations in hours is also worth considering.)

Ps. I'm not really familiar with Rails, but at least the first result I got when I Googled for "mysql rails enum" says:

"The best part from the Rails side, is that you don’t have to change anything at all in your code to swap a varchar out for an ENUM."

Upvotes: 0

Related Questions