Results 1 to 6 of 6
  1. #1
    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,

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's better to say NOT NULL and then store 0

    NULLable columns require an extra null bit of storage space
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    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.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,569
    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 on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,569
    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 on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •