php - MySQL Fields alternative to 'is_active', 'is_banned' flags?
I've encountered a table has ~20 fields of 'is_X' (is_active, is_banned, is_allowed_to_view_something and so on) and it seems just plain wrong.
I am familiar with the bitwise method by storing an INT in one field and then breaking it to bits and using it as flags but is there any other way to store a lot of information (most of it is yes/no) in a MySQL table without cluttering the table with tons of fields?
It is good practice to use statuses column. instead of single column for every status. It can be done by serializing object with user's statuses or just simply saving JSON.
easy to manage
your table gets smaller
Bit columns are usefull because easy to use.
An other way could be a right table (I assume you are working on a user table)
rightis supposed to store multiple rows which are
banned, etc ...
user_has_righthas 2 foreign keys :
fk_id_right. If an entry exist, then the user has the right to ...
Example : Suppose you want all active users. Suppose
id_rightfor active users is 1.
MySQL does have a BIT type, but all the bit functions and operators currently require
BIGINT(64-bit) arguments and returns. If you can live with the cast to/from
BIGINT, you can use any sufficiently large (for your application) integer type and use bitmasks with bitwise
ands to set and clear (respectively) individual bits.
Clearly, the intended semantic of each bit-position is not explicit, and so less clear than individual
is_B, ... columns, but you might be able to ameliorate that somewhat with a table of set/clear bitmasks.
It depends on what you're doing, how you use the fields, etc. and (of course) on your own particular preferences, biases, etc. on where the balance lies and whether one is better than the other for a particular situation.
Also, if you Google around, and you'll find that there may be some issues and bugs with
BITin older versions (around 5.0.x) of MySQL.