In my website, I need to keep track of whether an Application was approved or not.

To me, there are really 3 states, though.

1.) The Application has not been seen so the "app_approved" field would be "Null"
2.) The Application has been seen and everything was okay, so the "app_approved" is "True"
3.) The Application has been seen and everything was NOT okay, so the "app_approved" is "False"

**Disclaimer, I am actually using MySQL, but I like you guys over here at UA so much I figured I'd come visit!

In MySQL, believe it or not, there actually is no "BOOLEAN" data-type yet?! So the conventional wisdom is to use "TINYINT" which is something like 0-255 unsigned and choose "0" for "False" and "1" or any other positive number for "True"

I was sorta thinking of doing this...

0 = Null
1 = True
2 = False

...but I am not sure of the implications?!

If I use a Lookup Table, I don't think my PHP code will care what I call each "State", but since this is likely a fairly common problem, I am wondering if there is a better way to handle things?


If you were doing this in MS Access or SQL Server how would you handle my 3-State problem?

And if you were doing this in MySQL - based on what I shared - what do you think would be the best approach?

Sincerely,


Robert

I agree with your 0,1,2 for storing the information. In your sql statement, I would just use either a couple of embedded if statements or a Case statement to replace the numerics with strings.

In Access I would use the smallest integer field possible, --- Byte.

I would store Null for null, -1 for True, and 0 for False.

In MySQL I would probably use 1 for True, (as I believe that is the standard way SQL Server represents True). I know you're dealing with MySQL, but it is probably closer to SQL Server than to Jet.

Actually MySQL will interpret any non-zero value as True.

(And I believe MySQL has Boolean, Bit, and TinyInt)

S-Q-L and most languages interpret "Null" as "False" and any positive number as "True" so relying on the native implementation doesn't help.

I am just asking if using...

0 = No Answer (aka Null)
1 = True
2 = False

... is okay if that is how I define things in my Lookup Table and in my PHP code. (That is, I'm not asking S-Q-L's advice!)


Robert

>>S-Q-L and most languages interpret "Null" as "False"<<

I would have to disagree with that. Null is very different than False.

>>and any positive number as "True"<<

Here I would say "usually any non-zero value" would equate to True (other than Null).

But 0,1 & 2 is fine if you have defined them that way in your tables. It's just easier to combine these results or evaluate them in VBA or other languages when you stick to a standard.

Null is very different from "False", but in MySQL...






Okay, thanks.


Robert

>> In MySQL, 0 or NULL means false and anything else means true. <<

In Jet, the boolean datatype (Yes/No in the Access UI Table Designer) cannot store Nulls, so if a Null write to a boolean field is attempted the Jet/ACE database engine silently coerces the Null to a False for storage. Also note that, IIRC, a bool in MySQL is a synonym for Tinyint(1). So, while Null may be able to be stored, the application interfacing with your data may not be able to understand the concept of a Null boolean. For example Access with an ODBC linked table that points to a SQL Server table with a bit field will map that field as boolean (Yes/No), and since Access (Jet/ACE) doesn't understand that concept, so errors will occur when modifying records with "null" booleans at the source. To counteract that issue, the BIT column is declared as 'required' with a default of either a 1 or 0.