SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2002
    Location
    Illinois
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb NULL or NOT NULL that is the question?

    I have a database that just seem to keep growing on me, by growing I mean more and more fields in my tables. The problem I am having is that as I am updating my PHP scripts to accomidate these new field names in the tables they are defaulting to a Null value, and my script then doesn't show any records. as soon as I add some value in the new fields the script shows up fine.

    The question is: Is it better to set the fields NOT NULL and deal with some bogus defaul value or just leave it NULL and make my script deal with NULL.

    If its better to leave it NULL can you post some code examples on how to deal with fields that are NULL, with select, update, insert querys & mysql_fetch_array .

    Thanks
    CC
    -- Hit any key to continue. "Where's the any key?" --

  2. #2
    SitePoint Zealot
    Join Date
    Jul 2002
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Firstly, I am not an expert or anything but I have read some stuff about optimization lately.

    If I remember correctly, to make your table more efficient, you should indicate columns that are NOT NULL (whenever possible). For example, a primary key column should technically be always full. So why not set this to NOT NULL.

    If there is a chance that a field can be empty, I would simply set it to NULL to avoid and "bogus" value. If do you have NULL columns, you can set a default value. If you want to test if some extracted data is NULL, all you have to do is use "if" statements (OR OTHERS) to test the condition. Of course, your condition will be testing for the 'default value' you have set up.

    Hope I understood you right...

    Spaz
    Just a little boy trying to make his way into the world...
    ~~~Spaz Boy
    Programmer + Gamer = ProGamer

  3. #3
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Moved to a more appropriate forum

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)

    it depends...

    NULL or NOT NULL depends on what you want to achieve

    i know how much that sounds like a cop-out

    CC, your specific problem,
    The problem I am having is that as I am updating my PHP scripts to accomidate these new field names in the tables they are defaulting to a Null value, and my script then doesn't show any records. as soon as I add some value in the new fields the script shows up fine.
    sounds like the script needs work

    let's say you have a Student table with ID primary key (so, not null), Name not null, and Grade null numeric

    if you run SELECT Name,Grade you can expect to get rows back where the Grade is null -- this does not mean zero

    in fact, when you run SELECT AVG(Grade) you will get the correct average, i.e. there will be no distortion due to a "default" value of zero, which now you can't tell whether the guy actually pulled a zero or didn't write the test

    NULL has some very good benefits, so you have to decide at the outset whether you want it

    granted, when you ALTER TABLE ADD COLUMN, it has to start null, or else have a default, but there are ways to deal with that too

    years of experience have taught me to use nulls for what they were intended for

    post again if there's anything more specific...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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