Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Mar 2005
    Location
    Brighton, UK
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NULLs and Empty Strings, treating them equally?

    Hi,
    I've built a database class in PHP which, when given an empty string for a field value, converts it to NULL, simply because although their technically not the same thing, as far as useful data goes, they are equally useless.

    I want to use the NOT NULL flag on fields to dictate which fields should have data, and which shouldn't, and really, an empty string is no data.

    I was just wondering what other peoples opinion was on this, and weather or not it is a reasonable restriction to enforce, I cant think of any situation where data is required for a field (NOT NULL), and an empty string would be a reasonable value.

    Thanks,
    Jack
    Last edited by Jak-S; 03-01-2007 at 09:40 PM.

  • #2
    Regular Coder ralph l mayo's Avatar
    Join Date
    Nov 2005
    Posts
    951
    Thanks
    1
    Thanked 31 Times in 29 Posts
    I would say doing this in PHP or whatever other client software is bad. It's not idiomatic and doesn't follow the principle of least astonishment. I'd prefer to either insert the data as is and use a view with a virtual field something like COALESCE(fieldname, '') <> '' AS valid or to make a trigger function on insert or update that triggers an error on the empty string.

  • #3
    Regular Coder
    Join Date
    Mar 2005
    Location
    Brighton, UK
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ralph l mayo View Post
    I'd prefer to either insert the data as is and use a view with a virtual field something like COALESCE(fieldname, '') <> '' AS valid or to make a trigger function on insert or update that triggers an error on the empty string.
    Isn't this overkill for such a simple restriction, especially considering it applies to every NOT NULL field? The code that does the check, and changes the value to NULL is only within the database access classes, and that is the only point into the database.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    I have found through experience that working with null fields (in other databases, not MySQL) is a realy pain in the arse. I would turn it around on you and ask, what's the point of messing with nulls? What do you gain?

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Quote Originally Posted by Jak-S View Post
    Hi,
    I've built a database class in PHP which, when given an empty string for a field value, converts it to NULL, simply because although their technically not the same thing, as far as useful data goes, they are equally useless.
    NULL is significantly different than an empty string. an empty string does not belong in a table, NULLs do.

    Averaging a bunch of test scores? a 0 or an empty string would throw off the data, a NULL does not.

    Need to enter unknown information in a unique field? you can do that once with an empty string. As NULL is not equivalent to anything else, even another NULL you can enter many rows of NULLs.

  • #6
    Regular Coder ralph l mayo's Avatar
    Join Date
    Nov 2005
    Posts
    951
    Thanks
    1
    Thanked 31 Times in 29 Posts
    Quote Originally Posted by Jak-S View Post
    Isn't this overkill for such a simple restriction, especially considering it applies to every NOT NULL field? The code that does the check, and changes the value to NULL is only within the database access classes, and that is the only point into the database.
    In my view it shouldn't apply to every NOT NULL field; if you want to treat empty strings as NULL you *should* have to go out of your way because it is exceptional to the general rule and such automatic behavior would defy the expectations of most everyone. '' isn't NULL (C string semantics notwithstanding) and I'd consider a database abstraction library that treated it as such broken.

  • #7
    Regular Coder
    Join Date
    Mar 2005
    Location
    Brighton, UK
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I totally understand that NULLs and empty strings are two distinctly different things, but an empty string is essentially useless data, or really no data at all.

    In a database, where the reliability of the data is the most important thing, if you set a field to NOT NULL, your basically saying that this data is required, and a record cannot be created without it. What would be the use in that restriction, if people could just fill the field value with an empty string? Sure, it isn't NULL, but its still nothing.

    Think about other field types, say an INTEGER. The equivalent to an empty string in an integer field is 0, but the value 0 is meaningful. If a roomsAvaliable field was set to 0 then you know that no rooms are available, but if it were NULL, then you would know that the number of rooms available is unknown.

    When it comes to a string field, say CHAR, an empty string isn't meaningful. If there was a field called firstName, and it was NULL, you would know that the first name is unknown, but if it were an empty string, the database would think it had a value, it wouldn't be unknown, but in reality the value is useless.

    I'd consider a database abstraction library that treated it as such broken.
    That's fair enough, given that MySQL does allow empty strings in a NOT NULL field, I would agree that any code to stop you doing it wouldn't be a good idea, but I still don't really see the point in MySQL allowing it.

    Given that I am dealing purely with form input here, I will probably move the code that enforces this restriction from the database classes into the form processing classes. If someone doesn't fill in a form field, its not because they want to give an "empty string" value, its because they don't want to give a value at all. And given that any empty field will always come through in the _POST array as an empty string, there is no way to distinguish between the two.


  •  

    Posting Permissions

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