Use of MySql Data Types

Question

Using the following as an example I am trying to figure out the best way set up a MySql database.

FoodGroupTypeCode: Grain, Vegetable, Fruit, Dairy, Protein

What is the best Data Type to use to represent this attribute?

1. CHAR(1): G, V, F, D, P
2. TINYINT: Using number codes (i.e. Grain = 1, Vege = 2)
3. ENUM
4. VARCHAR(9): Using full names
5. Other (explain)

Option 4 is really not a consideration of mine, unless someone can really validate it. I have read a lot of "opinions" on the subject but looking for more concrete reasons for picking one over the other. I appreciate any input that someone can give to that degree rather then something such as "I like ENUM because it is fast."

Answer 1

CHAR(1): G, V, F, D, P
Performant, storage efficient and results in readable code. I favour this solution when I know that the values are stable, and when I need to attach behaviour to the codes. (if code = 'G' then specific behaviour).

TINYINT: Using number codes (i.e. Grain = 1, Vege = 2)
Performant, storage efficient, but results in less readable code. This solution is also immune to confusion if values change (G was initially Grain, but now it is changed into Brain). This is most common.

ENUM
I don't see a single benefit of using enum other than a tiny performance benefit. Of course, the small performance gain will never be enough if you are truly having a performance problem.

VARCHAR(9): Using full names
Results in readable code. Certain queries will have less joins which in those specific cases provides performance benefits. Not storage efficient, and may give you problems if you want to change the values. If you have lots of tables with lots of rows and they are all referencing this table, think carefully before you take the risk.

Answer 2

I would recommend ENUM, because it would constrain your possible selections to Grain, Vegetable, Fruit, Dairy, Protein.

However, in the database I usually do this as a VARCHAR(2) with a Check Constraint (can only be Grain, Vegetable, Fruit, Dairy, or Protein), then represent it in my app as an ENUM or List.

Answer 3

The choice of appropriate datatype entirely depends on how you'll be dealing with the table data in your application (if any), if you're having this table just for storing the records than VARCHAR would be a better option, since it'll make your data more verbose, but if there's a processing to be done on the table data like, filter results on certain criteria, long names could be confusing when it comes to comparing them in the application logic, and in such situations, CHAR or TINYINT would be a better option. While the performance impact based on the datatypes used should be a second priority.

Answer 4

I'd use CREATE TABLE FoodGroupType (id int unsigned not null auto_increment PRIMARY KEY, code VARCHAR(20), description VARCHAR(100). So Food table will look

CREATE TABLE Food (id int unsigned not null auto_increment PRIMARY KEY, 
food_group_type_id int unsigned not null, name VARCHAR(50), description VARCHAR(100),
FOREIGN KEY(food_group_type_id) REFERENCES FoodGroupType(id) ON DELETE ... ON UPDATE ...

Answer 5

I personally would not go with Char(1) or tinyint because they are not descriptive. Looking at the data it is not as clear as it could be. Varchar and enum would be the two I would consider. I like the idea of enum though, as that is really what it is. You are saying all foods have to be one of these food types.

Plus I hear enum is fast!