Mysql – Booleans, CONSTANTS or mapping table for ‘status’-like fields


I am modelling a User table which needs to have the following information about the users:

  • is_active?
  • is_detained?
  • has_voluntarily_deactivated?
  • is_temporarily_suspended?

and so on…

Basically, these are boolean flags with true or false. So, I am considering few approaches other than boolean flags which are as follows:

  1. Create a single varchar field with values like 'active', 'detained', 'deactivated', 'suspended', etc.
  2. Create a tinyint field and map the integers to another table containing status strings
  3. Create a tinyint field and map the integers in code itself using constants, such as ACTIVE = 1, DETAINED = 2, etc. Is Python's enum type the best solution to this?
  4. Create a tinyint field and map the integers to status strings in an XML or JSON file

Which of the above 4 or the original boolean style approach is preferable, or if there could be a completely different approach or a modified version of the above approaches, please let me know?

Also, in my code, how should I call these fields, like:

  • if (user.status == 1), or something like
  • if (user.status == STATUS.ACTIVE), or
  • if (user.status == 'active')

(I think this will depend on which approach I follow)

These status values are not limited and may be added, edited or removed in future. Request you to answer in a database agnostic way and the programming language that I am using is Python.

Thank you for your answers

Best Answer

In MySQL, you could use ENUM or SET, depending on whether the states are mutually exclusive. That is, can it be both 'active' and 'detained'?

ENUM('active', 'detained', 'deactivated', 'suspended')


SET('active', 'detained', 'deactivated', 'suspended')