Re: null data in table question
Hi, don't forget to apply the correct changes to your queries. Having NULLs or not, let you write for example : select ... from ... where midinials is NULL; And be aware about NULL indexation in some storages. Those values are not indexed for example in oracle. I'm not sure about innodb, but this sould be. In all the cases, you can't have a unique index on such columns. Mathias Selon Martijn Tonies <[EMAIL PROTECTED]>: > Hi Scott, > > > 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? > > Given that NULL means "unknown" and there's no value/state for "non > applicable", NULLs don't belong in places where you actually want to fill in > "nothing" or "empty". > > An empty string is an empty string. Why not insert that instead? > > With regards, > > Martijn Tonies > Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL > Server > Upscene Productions > http://www.upscene.com > Database development questions? Check the forum! > http://www.databasedevelopmentforum.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: null data in table question
Hi Scott, > 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? Given that NULL means "unknown" and there's no value/state for "non applicable", NULLs don't belong in places where you actually want to fill in "nothing" or "empty". An empty string is an empty string. Why not insert that instead? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: null data in table question
"Scott Purcell" <[EMAIL PROTECTED]> 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 question
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
