2

Marginally related to Should I delete or disable a row in a relational database?

Given that I am going to go with the strategy of warehousing changes to my tables in a history table, I am faced with the following options for implementing a status for a given row in MySQL:

  • An isActive booelan
  • An activeStatus enum
  • An activeStatus INT referencing a small ActiveStatus lookup table
  • An activeStatus INT not referencing another table

The first approach is rather inflexible in my opinion, since I might need more booleans in the future to support other types of active statuses (I'm not sure what they would be, but maybe something like "being phased out" or "active for a random group of users", etc).

I'm told that MySQL enum is bad, so the second approach probably won't fly.

I like the third approach, but I'm wondering if it is a heavy handed solution to a relatively small problem.

The fourth approach requires that we know in advance what each status INT means and seems like an outdated way to do things.

Is there a canonical right answer? Am I ignoring another approach?

Community
  • 1
  • 1
tacos_tacos_tacos
  • 10,277
  • 11
  • 73
  • 126

2 Answers2

2

Personally I would go with your third option.

Boolean values often turn out to be more complex in reality, as you suggested. ENUMs can be nice, but they have the downside that as soon as you want to store additional information about each value - who added it, when, is it only valid for a certain time period or source system, comments etc. - it becomes difficult, whereas with a lookup table those data can easily be maintained in additional columns. ENUMs are a good tool to constrain data to certain values (like a CHECK constraint), but not such a good tool if those values have significant meaning and need to be exposed to users.

It's not entirely clear from your question if you plan to treat your history table like a fact table and use it in reports, but if so then you could consider the ActiveStatus lookup table as a dimension. In this case a table is much easier, because your reporting tool can read the possible values from the dimension table in order to let the user choose his query conditions; such tools generally don't know anything about ENUMs.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
1

From my point of view your 2nd approach is better if u have more than 2 status.Because ENUM is great for data that you know will fall within a static set. But if u have only two status active and inactive then its always better to use boolean.

EDIT: If u r sure in future u r not gonna change the value of your ENUM then its great to use ENUM for such field.

Ranjit Singh
  • 3,715
  • 1
  • 21
  • 35