"Scott Purcell" <spurcell@stripped> wrote on 07/05/2005 04:21:06 PM:
> 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.
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.
Unimin Corporation - Spruce Pine