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?
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
ENUM('unknown', 'visible', 'private', 'private-with-password') NOT NULL
- It will take 1 byte (the minimum);
- Odd things can deliberately (or accidentally) become
- No extra table needed;
- The code will have spelled out strings.