0

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

3
  • 1
    As not all DBMSes support Boolean a datatype, Database agnostic would be a status CHAR(1) NOT NULL CHECK (status in ('Y','N')), or a single byte INT (doesn't exist in Standard SQL, but most DBMSes support a variant) CHECK (status in (0,1)). Mapping could be a simple CASE status WHEN 0 THEN 'active' ELSE 'NOT ACTIVE' ENDin a view
    – dnoeth
    Sep 18, 2016 at 13:38
  • But my question is should I be using 'Y' or 'N', or 0 or 1 for each field, such as active status, suspended status, detained status or simply a single status field with integers 1, 2, 3 and so on... representing active, suspended, detained respectively? Please feel free to make any edits to my question if it needs some further understanding. Thank you :) Sep 18, 2016 at 13:45
  • I would prefer one column per status.
    – dnoeth
    Sep 18, 2016 at 13:55

1 Answer 1

0

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')

or

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.