What is the canonical/best way to implement a simple true-false toggle for a database

For example a user preference like "display my email to other users" which they can set or unset with a checkbox.

It's a single bit of information, so do you set the field as INT(1) or BOOLEAN, do you set it as one or zero (with a default of zero), or as one or NULL. How about a SET with two possible values, 'yes' and 'no'?

I can't see any huge differences between them, or gotchas. What's best practice?

EDIT: fo开发者_开发百科rgot to say, the language with which you test that value might be a factor. If you have a built-in undef or false so you can test with

if($show_email)

is that better than

if($show_email == something)

?

NULL should represent "I do not know the answer", whereas True or False should represent "I know the answer, and it is [True | False]"

Whether there are benefits to storing the "bit" as a specific data type may depend on the specific database platform you are using. For DB platforms without an explicit Boolean data type, it's probably a safe approach to use the smallest integral (e.g. TINYINT) data type if you don't have better guidance for that platform.

I think I'd prefer a 1 or zero myself. And that'd probably work on a lot of database servers (and a lot of apps.) I'll just toss out that I also see CHAR(1) with 'y' or 'n' used a lot. Which of course means you have to check for case and all that, but it does save a little space if that's a concern.

I personally wouldn't allow NULLs. That adds a bunch more conditions to check for at the app level, doesn't it?