What is the best way to save the status of a record in a MySQL table beyond simple 0 or 1?

Question

If I have a table full of records, they could be payments, or bookings or a multitide of other entities, is there a best practice for saving the status of each record beyond a simple 0 for not active and 1 for active?

For example, a payment might have the status 'pending', 'completed' or 'failed'. The way I have previously done it, is to have another table with a series of definitions in value/text pairs ie. 0 = 'failed', 1 = 'pending' and 2 = 'completed'. I would then store 0, 1 or 2 in the payments table and use an inner join to read the text from the definitions table if needed.

This method sometime seems overly complicated and unnecessary, and I have been thinking of changing my method to simply saving the word 'completed' directly in the status field of the payments table for example.

Is this considered bad practice, and if so, what is the best practice?

O. Jones O. Jones · Accepted Answer · 2014-06-19T12:46:04

These seem to be transaction records, so potentially there are many of them and query performance will be an issue. So, it's probably smart to organize your status column or columns in such a way that compound index access to the records you need will be straightforward.

It's hard to give you crisp "do this, don't do that" advice without knowing your query patterns, so here are a couple of scenarios.

Suppose you need to get all the active bookings this month. You'll want a query of the form

 SELECT whatever
   FROM xactions
  WHERE active = 1 and type = 2 /*bookings*/
    AND xaction_date >= CURDATE() - INTERVAL DAY(CURDATE()) DAY

This will perform great with a compound BTREE index on (active,type,xaction_date) . The query can be satisfied by random accessing the index to the first eligible record and then scanning it sequentially.

But if you have type=2 meaning active bookings and type=12 meaning inactive bookings, and you want all bookings both active and inactive this month, your query will look like this:

 SELECT whatever
   FROM xactions
  WHERE type IN (2,12)
    AND xaction_date >= CURDATE() - INTERVAL DAY(CURDATE()) DAY

This won't be able to scan a compound index quite so easily due to the IN(2,12) clause needing disjoint ranges of values.

tl;dr In MySQL it's easier to index separate columns for various items of status to get better query performance. But it's hard to know without understanding query patterns.

What is the best way to save the status of a record in a MySQL table beyond simple 0 or 1?

3 Answers