List:General Discussion« Previous MessageNext Message »
From:SGreen Date:July 5 2005 8:48pm
Subject:Re: null data in table question
View as plain text  
"Scott Purcell" <spurcell@stripped> wrote on 07/05/2005 04:21:06 PM:

> Hello,
> I have created a web-based simple application, and used mysql for 
> data storage. All has worked well. But I do have a simple question I
> would like to ask the group.
> I have some web-based forms that match table structure. Most of the 
> important fields have validation, and I ensure good data into the 
> table. But I have a few columns in the table such as "middleInitial"
> where I do not validate the data. And in the database is shows a 
> null when I do a select * from.
> Is a null acceptable in the database, or is there something I should
> do on columns that the user may not put in data?
> Currently my tables are simple like so.
> CREATE TABLE `user` (
>   `id` int(11) NOT NULL auto_increment,
>   `modified_date` timestamp(14) NOT NULL,
>   `created_date` timestamp(14) NOT NULL,
>   `username` varchar(50) NOT NULL default '',
>   `firstname` varchar(25) default NULL,
>   `initial` char(1) default NULL,
>   `lastname` varchar(25) default NULL,
>   `company` varchar(50) default NULL,
>   `address1` varchar(50) default NULL,
>   `address2` varchar(50) default NULL,
>   `city` varchar(50) default NULL,
>   `state` char(2) default NULL,
>   `zip` varchar(5) default NULL,
>   `phone` varchar(12) default NULL,
>   `eveningPhone` varchar(12) default NULL,
>   `email` varchar(50) default NULL,
>   `password` varchar(50) default NULL,
>   `admin` char(1) NOT NULL default 'F',
>   `hintchoice` char(1) default NULL,
>   `hintvalue` varchar(50) default NULL,
>   PRIMARY KEY  (`id`)
> ) TYPE=InnoDB;
> Thanks for any input, as I am learning.
> Scott
There are two basic philosophies when it comes to NULL values in a 
database. The first group says "No! Never! How dare you ask such a thing 
you lazy sot!". The other group recognizes that real data cannot always be 
"complete" and that NULLs are a convenient way to represent such an 
occurrence. I happen to fall into the second group.

What you have to remember about NULL information is that it represents 
"nothingness", a state of being, NOT "nothing" the value. Your missing 
middle initial is an excellent example of why NULLs are acceptable. If 
someone has a middle initial, you can easily store it (some people have 
more than one but I will let you figure that one out on your own... you 
did say you were learning, right?). If you know that someone doesn't have 
a middle initial you can also store that fact as the empty string ''. The 
fact that you just do not know if a person has a middle initial or not 
would be represented by the value NULL. Notice that NULL is not ''. One is 
a value (a string with no letters in it) the other is a condition 
representing the lack of information (NULL).

How you deal with missing data is entirely up to you. In my humble opinion 
any field for which you absolutely do not need a value is potentially a 
NULL-able field. Does that mean I will always make them null? No. What 
gets stored in a database is sometimes determined by the programming 
languages and the needs of the user interface or other users of the data. 
In your case, I think you have a good first design and you shouldn't worry 
about the NULL values unless you really need to for other reasons.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

null data in table questionScott Purcell5 Jul
  • Re: null data in table questionSGreen5 Jul
  • Re: null data in table questionMartijn Tonies5 Jul
    • Re: null data in table questionmfatene6 Jul