Hi,

I have database tables with many TINYINT columns that check for statuses i.e. status=0 or status=1. How good practice is it to store NULL value as default instead of '0' which would mean 'not enabled' since my queries only check for status=1, not for status=0.

Also, for tables with very large # of rows how much storage space will it save, and will it help MySQL to process more data in less time?

Thanks,

it's better to say NOT NULL and then store 0

NULLable columns require an extra null bit of storage space

Thanks, it means a column should only be 'Nullable' if you ever need to know that there was 'no value' stored, not even an empty value.

NULL means there is no known value for this column, for as long as the value is NULL. for your application I would have thought the BOOL/BOOLEAN dattype would be the appropriate one. BOOL maps to TinyInt

Bool will work fien for flags that are either or values
In your case if true = enabled then false = not enabled (and that should be the default value). however that depends on your data. if you have situations where that attribute isn't appropriate then NULL could be a reasonable way out. however it could also suggest that your design is flawed and the attribute shouldn't be part of the same table.

this is a splendid and simple example of the main argument used by people who think NULLs should be avoided completely

"shouldn't be part of the same table" is easy to understand when related to the primary key, because the primary key is exactly what gives the table its unique identity

if you have an attribute that would require a NULL sometimes in this table, then this situation implies that the attribute does not depend on the (entire) primary key

thus you need a (likely related) separate table that does have the appropriate primary key where this attribute will never need to be NULL

now, i personally don't mind NULLs, they can be useful

but i also agree with healdem, when you want to store 1=yes, 0=no, then allowing NULL is a warning flag

Actually Im an enthusiastic adopter of NULL's. I do not subscribe to the use of NULL is BAD/EVIL/directive from the Sith or whatever.

However I have seen people wrap up a portmanteau of properties/columns into a single table rather than design the tables properly in the first place. in some cases using NULL's in what is ostensibly a boolean column could mean the tablle desing may need spliting using the sub type approach.

I'm not saying the use of NULL is evil, just that sloppy use can be a symptom of suspect design.