Best practice for fixed number of strings in MySQL?

Question

Say i have a datatype called "status" as a row in a mysql table. Status can only be a fixed number of strings, say "active", "inactive" and "pending". What datatype is best practice to use?

• Make another table "statuses" and have an pointer id in my table?
• Make a php array containing the different statuses and use my status-row as index for the array?
• Simply letting status be a string containing the current status?

Answer 1

Specifically for exactly that purpose is the ENUM type: http://dev.mysql.com/doc/refman/5.5/en/enum.html

CREATE TABLE ... (
    status ENUM('active', 'inactive', 'pending')
    ...
);

Answer 2

If the set of statuses is fixed at development time you definitely want to use ENUM. Another case is when possible statuses can be added in runtime. In this case you want to use separated table to store them and foreign key to check that all statuses are valid.

Using simple string for this is a kind of bad practice. E.g. just a minor misspelling in status can break everything related to the particular row.