-1

We need to store some fields like state where we want to store things like published, waiting-moderation, or a field visibility where we want to store things like visible, private, private-with-password.

I believe it is best to store this kind of fields in varchars instead of tinyint for booleans because it makes clearer our data structure, but some of my teams argues that you can make it clear with a comment and benefit with the performance boost of tinyint.

I have some real examples of well known software who also does it this way, like WordPress with its status value for posts.

Would the performance be too much compromised with my point of view? What is the standard method for this kind of field?

  • It depends. Is this a couple of columns on a few tables? Probably won't matter a ton. I'd prefer option 3: have FKs to a status table so you don't have IsPublished, IsWaiting nor "waiting", "published". Its easy to say "I need 3 bool columns" and then a year later find out you have 8 which are a PITA to query/manage. Normalize your data and leave room for future changes! – LowlyDBA Mar 9 '18 at 16:59
  • I have 2 columns in 1 table with this approach. Your third option would need to have diferrent tables for different purposes or do you use just one to store for example visibility values and states values? – codiaf Mar 9 '18 at 17:09
  • If they're describing different things, they'd be different tables. – LowlyDBA Mar 9 '18 at 18:37
0

It depends.

There is a 3-way debate -- VARCHARs vs ENUMs vs TINYINTs. Each has pros and cons -- space, clarity, error checking, frequency/ease of changing the list, etc.

There is no clear winner; I suggest you flip a 3-sided coin to make the decision, then learn from your experience.

And/or, you could search dba.stackexchange and stackoverflow for [mysql] ENUM VARCHAR TINYINT to see some of the discussions.

Personally, I would probably use this for visibility:

ENUM('unknown', 'visible', 'private', 'private-with-password') NOT NULL

Notes:

  • It will take 1 byte (the minimum);
  • Odd things can deliberately (or accidentally) become unknown instead of NULL;
  • No extra table needed;
  • The code will have spelled out strings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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