SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    What to store for Blank Fields?

    When a user leaves a Form Input Box or Text Area blank, what should I store in my database?

    Conventional wisdom would say a NULL since they never - in this scenario - filled out the field.

    I believe that for Inputs and TextAreas, HTML inserts an Empty String into the $_POST array, so by default you would be writing Empty Strings into your database.

    Right now I am checking all of my Form Validation, and I want my PHP to insert the *proper* data-type into fields whether that should be a NULL, Empty String, or 0.

    Suggestions?

    Thanks,


    Debbie

  2. #2
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    It's NULL for empty strings or for (accidental) series of spaces or tabs.

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by itmitică View Post
    It's NULL for empty strings or for (accidental) series of spaces or tabs.
    I don't understand what you said.

    Are you saying that I should insert a NULL for Form Controls that yield an Empty String (e.g. Text Boxes and text Areas)?


    Debbie

  4. #4
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Yes, empty strings, (accidental) series of spaces or tabs should be NULL values in your database because otherwise you may get undesired results in your SQL.

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by itmitică View Post
    Yes, empty strings, (accidental) series of spaces or tabs should be NULL values in your database because otherwise you may get undesired results in your SQL.
    Backing up, some context into what I am doing.

    Currently I am checking my validation code for my Change Member Details form which contains a lot of fields which are optional (e.g. Gender, Location, Year Born, Hobbies, etc).

    When I look at my "member" table currently in phpMyAdmin, I see a lot of cells that are blank indicating that the User did not answer these Form Fields and so an Empty String got written into said fields.

    If I understand you correctly, you are telling me that is bad, right?

    And you are saying that when I do my INSERT, I should always insert a NULL into such fields, right?

    Is that correct?


    Debbie

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    In MyISAM tables there is no storage difference between NULL and an empty string. However, in InnoDB tables there is a difference as empty strings are not included in the overall record.

    Check this out.

    From a programming side you will need to convert your empty string (trimmed) into a NULL value. Likewise if you are searching you will also need to change the query to use IS NULL or IS NOT NULL rather than an empty string.

  7. #7
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    If I understand you correctly, you are telling me that is bad, right?

    And you are saying that when I do my INSERT, I should always insert a NULL into such fields, right?

    Is that correct?


    Debbie
    Yes. A user could very well insert by accident a number of whitespace characters in those fields. As value, these whitespaces are nothing. From a database point of view nothing is NULL, something undefined.

    If you insert empty strings for them, that's not nothing, it's still something and it's very well defined. Furthermore, when you're SQLing you want to keep to the standards (NULL for nothing) and hopefully keep any single row functions, like trimming, out of it.

    And also because checking against NULL is usually faster than checking an empty string in most databases.

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by itmitică View Post
    Yes. A user could very well insert by accident a number of whitespace characters in those fields. As value, these whitespaces are nothing. From a database point of view nothing is NULL, something undefined.

    If you insert empty strings for them, that's not nothing, it's still something and it's very well defined. Furthermore, when you're SQLing you want to keep to the standards (NULL for nothing) and hopefully keep any single row functions, like trimming, out of it.

    And also because checking against NULL is usually faster than checking an empty string in most databases.
    So I should strive to either INSERT a NULL or a non-white-space value (e.g. 0, 1, Debbie, TRUE, etc) into all fields in my database, correct?

    Also, is it okay to have a series of fields defined as NULL-able and then have them full of NULL's?

    I think it is reasonable when you have fields like: Location, Hair Color, Weight, etc.

    For fields like Gender, I use 0=unknown, 1=male, 2=female, 3=other

    Is that okay?


    Debbie

  9. #9
    Non-Member
    Join Date
    Feb 2012
    Posts
    892
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)
    Yes, you should have only real values in your tables.

    If you think a series of fields would be left NULL by default, you should consider moving them to another table, like *_DETAILS, and to only insert in that table when real values occur.

    But that depends. It should not become an extreme normalization or significantly raise the complexity level for your SQL.

  10. #10
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by itmitică View Post
    Yes, you should have only real values in your tables.

    If you think a series of fields would be left NULL by default, you should consider moving them to another table, like *_DETAILS, and to only insert in that table when real values occur.

    But that depends. It should not become an extreme normalization or significantly raise the complexity level for your SQL.
    I agree with the moving of things like msn, yim, etc nicks, hobbies, avatars and fields like that to a seperate table 1-to-1 joined with the users table. If you take a look at the profile pages of a random selection of users on any given forum, generally the majority don't bother with them fields.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  11. #11
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    I agree with the moving of things like msn, yim, etc nicks, hobbies, avatars and fields like that to a seperate table 1-to-1 joined with the users table. If you take a look at the profile pages of a random selection of users on any given forum, generally the majority don't bother with them fields.
    And what is the benefit of doing that?

    I've always heard that splitting a table into two tables that have a one-to-one relationship is hokey...


    Debbie

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I've always heard that splitting a table into two tables that have a one-to-one relationship is hokey...
    no, it's not hokey

    there are several good reasons to do it, i'll give one example...

    let's say a table has 30 columns, and your most frequently run queries access only 10 of these columns

    splitting the infrequently used 20 columns off into a 1-to-1 table means that retrieval of the main table, without joining to the 1-to-1 table, is improved
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, it's not hokey

    there are several good reasons to do it, i'll give one example...

    let's say a table has 30 columns, and your most frequently run queries access only 10 of these columns

    splitting the infrequently used 20 columns off into a 1-to-1 table means that retrieval of the main table, without joining to the 1-to-1 table, is improved
    So you are saying that Table "width" impacts query performance?

    BTW, how do you link the two tables?

    Does the second table have a foreign key back to the primary table?


    Debbie

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    So you are saying that Table "width" impacts query performance?
    yes, absolutely it does

    but then, so does performing a join impact query performance

    Quote Originally Posted by DoubleDee View Post
    BTW, how do you link the two tables?

    Does the second table have a foreign key back to the primary table?
    bingo

    the second table's FK to the first table is also the second table's PK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, absolutely it does

    but then, so does performing a join impact query performance
    I remember hearing that back in a database class eons ago, and I brought it up with some Microsoft people I know and they said I was full of hot air.

    Glad to hear that I wasn't imagining things after all?!



    bingo

    the second table's FK to the first table is also the second table's PK
    So I would have something like...
    Code:
    member
    ------------
    - id (pk)
    
    
    member_attributes
    ------------------------
    - id (pk)(fk)

    Or another way to think of it, is that I would just have two tables with an "id" column with identically-matching values, right?



    Debbie

  16. #16
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    r937,

    Do you agree with my decision to INSERT a NULL value into fields like "location", "birth_year", "interests" when the user does not complete them in the HTML Form?


    Debbie

  17. #17
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    Pennsylvania
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would set the table constraint for that field to default to "NULL" unless a value is added.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Or another way to think of it, is that I would just have two tables with an "id" column with identically-matching values, right?
    yes

    Quote Originally Posted by DoubleDee View Post
    Do you agree with my decision to INSERT a NULL value into fields like "location", "birth_year", "interests" when the user does not complete them in the HTML Form?
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes

    yes
    So as a re-cap of my strategy for handling Form Fields which may be blank - especial ones that are often blank...

    1.) When a field has a finite number of choices (e.g. gender), try assigning a value for no response (e.g. 0=unknown, 1=male, 2=female)

    2.) When a field is larger and has a high probability of not being answered, attempt to create a one-to-many or many-to-many relationship so that there is one record for each response, and a record is only created when there is a value - as opposed to a lonely column with a bunch of NULL's. (I did this for my "Question & Answer" section which hold large responses, but which may often be left blank.) ( MEMBER -||-----0<- ANSWER ->0-------||- QUESTION )

    3.) If a field or a series of fields (e.g. About-Me section) has a high probability of being blank, consider breaking off said fields into a separate table which forms a one-to-one relationship with the main table (e.g. MEMBER -||-----||- MEMBER_ATTRIBUTES )

    4.) Use NULL's freely as long as they truly represent fields and values where the "value is unknown"


    How does that sound, r937?


    Debbie

  20. #20
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    357
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Keep in mind NULL values vs. empty string values can cause different results if you have tests that use logical operators. Also, NULL values can sometimes errors in your application code, i.e., in traditional asp if your db query returned a null value for myfield, and you used something like
    Code:
     if myfield = "something"
    you'd get an type mismatch error rather than just a boolean true or false returned.

    One place I used NULLs was to properly handle tri-valued checkboxes which could be checked, unchecked or grayed, where greyed indicated "never has been checked".
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon


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
  •