What to store for Blank Fields?

[DoubleDee]

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

[itmitică]

It's NULL for empty strings or for (accidental) series of spaces or tabs.

[DoubleDee]

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

[itmitică]

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.

[DoubleDee]

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

[rcashell]

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.

[itmitică]

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.

[DoubleDee]

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

[itmitică]

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.

[SpacePhoenix]

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.

[DoubleDee]

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

[r937]

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

[DoubleDee]

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

[r937]

So you are saying that Table "width" impacts query performance?

yes, absolutely it does

but then, so does performing a join impact query performance

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

[DoubleDee]

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

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

[DoubleDee]

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

[Darkwind]

I would set the table constraint for that field to default to "NULL" unless a value is added.

[r937]

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

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

[DoubleDee]

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

[Doug G]

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

 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".