If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Using NULL as default for INT columns

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2011
Posts: 32
Using NULL as default for INT columns

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,
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
it's better to say NOT NULL and then store 0

NULLable columns require an extra null bit of storage space
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2011
Posts: 32
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.
Reply With Quote
  #4 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,662
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.
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #5 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
Quote:
Originally Posted by healdem View Post
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #6 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 11,662
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.
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
Reply

Tags
null vs 0

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On