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)

user <=> user_has_right <=> right

Tableright is supposed to store multiple rows which areactive,banned, etc ...

Basically,user_has_right has 2 foreign keys :fk_id_user andfk_id_right. If an entry exist, then the user has the right to ...

Example : Suppose you want all active users. Supposeid_right for active users is 1.

SELECT * FROM user u
INNER JOIN user_has_right uhr ON uhr.fk_id_user = u.id_user
INNER JOIN rights r ON r.id_right = uhr.fk_id_rights
WHERE r.id_right = 1;



MySQL does have a BIT type, but all the bit functions and operators currently requireBIGINT (64-bit) arguments and returns. If you can live with the cast to/fromBIGINT, you can use any sufficiently large (for your application) integer type and use bitmasks with bitwiseors andands to set and clear (respectively) individual bits.

Clearly, the intended semantic of each bit-position is not explicit, and so less clear than individualis_A,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 withBIT in older versions (around 5.0.x) of MySQL.

People are also looking for solutions to the problem: php - Isolate an array column containing indexed rows of data, then flatten/merge to form an array of rows


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.