MySQL using numeric status codes vs text

Question

Often in my projects (PHP / MySQL) I have to add a "status" field to some database tables. For example to mark if a task if submitted, approved, rejected.

Out of habit so far I've been using numerical status codes (0 for submitted, 1 for approved, -1 for rejected). For some reason in my mind I assume it is more efficient for the server to process.

Now I am wondering, is my assumption flawed? Does it matter? For readability, it would be easier to store those as VARCHAR such as 'SUBMITTED', 'APPROVED', 'REJECTED'. Then in the PHP code instead of checking for the numeric value, check for the string.

I'm pretty sure that in theory checking for an INT is faster than a String. But is the different noticeable for a web app?

Please advise.

Thanks

Answer 1

Perhaps it's just a matter of preference but I hate magic numbers with no evident meaning. In this case, there are normally two situations:

• The values are unlikely to change (e.g., Sex: Male / Female)
• The values are likely to change (e.g., Category: Local / National / Sports...)

In the first case, I prefer to use ENUM as column type. It's handled internally as integer but you refer to it as string.

In the second case, I create a master table and define the child column as foreign key. That gives kind of meaning to the value, even if it's an auto-incremented integer. And it doesn't need to be an integer anyway.

Answer 2

Use ENUM to have the benefits of both approaches.

And if you want to go with pure numeric codes, don't compare them with numbers in PHP, define constants:

define( 'SUBMITTED', 1 );
define( 'APPROVED', 2 );
define( 'REJECTED', 3 );

if( $code == APPROVED ) {
    //do something
}