CODEWITHSUNDEEP
mysqlnullboolean
Michiel Thalen
Michiel Thalen

Reputation: 300

Checkbox value in mysql

We had a little discussion about how to save a newly introduced checkbox value inside our MySQL database. The checkbox can be 'on' or 'off' but sometimes is not shown at all. My idea is to simply save the status to a BOOL NOT NULL field. We then have either 0 or 1. My colleagues then said that wanted to include NULL, so we can see that no choice has been made.

I do think however that a BOOL field should never include NULL. You want it to but true or false, not unknown. If you really want to save the extra state of 'unknown', I would make the field a TINYINT with no selected = 0, selected = 1 and unknown = 2. This could also be an ENUM field.

How do you think about a BOOL field where NULL is possible?

Upvotes: 0

Views: 574

Answers (3)

BHAVIN PARMAR
BHAVIN PARMAR

Reputation: 9

boolean field can't have more than 2 values, boolean itself means 'either or' condition just like On or Off, True or False.

Upvotes: 0

CatShoes
CatShoes

Reputation: 3733

Personally, I see nothing wrong with using a NULL for a BOOL field. As far as I am concerned, it is a type like any other and can rightfully be assigned an unkown state. But if a BOOL can't be NULL because it is by defition either 'on' or 'off', then I would argue this logic can be extended to, say, Ints. Then, an Int can't be NULL because it must be a number.

Based on the other answers here, I am clearly in the minority on this. I should probably reconsider my position.

Upvotes: 1

Ryan
Ryan

Reputation: 1797

It's not a BOOL where NULL is possible, it's simply a state enum where 

0: not selected 
1: selected 
2: unknown/hidden

The enum is a good way to achieve what you're trying to do because if your application behaviour includes a 'hidden/unknown' state for the checkbox value then your mysql needs to reflect that or else you'll start hacking it around in the future.

Upvotes: 1

Related Questions