webJestic.NET

Database Designs: MySQL NULL Fields

Posted by Michael on June 10, 2009 · Leave a Comment 

Does your table designs support NULL values?

There is a debate about creating tables which permit NULL values. I personally make every effort to make every field in a table NOT NULL. The argument for this: I want to be able to depend on my SQL statements. In this post I will illustrate how an insignificant field that allows NULL values can create an issue. This is a simple illustration. More complex queries can break down as easily where NULL values are allowed.

It’s in my opinion, that NULL fields should be a topic for discussion, when you begin talking about your designs and the integrity behind those designs.

Example: NULL fields are allowed.

  1. CREATE TABLE SECURITY_USERS (
  2.        SECURITY_USER_ID Integer NOT NULL,
  3.        USER_NAME Varchar(30) NOT NULL,
  4.        FIRST_NAME Varchar(20),
  5.        LAST_NAME Varchar(20),
  6.        MIDDLE_INITIAL Varchar(1),
  7.  
  8.        CONSTRAINT PK_SECURITY_USERS PRIMARY KEY (SECURITY_USER_ID),
  9.        CONSTRAINT UNQ_SECURITY_USERS_USER_NAME UNIQUE (USER_NAME)
  10. ) TYPE=InnoDB DEFAULT CHARSET=utf8;

There are several things wrong with this table, but for the moment I would like to simply use it as an example of a table that supports NULL values. There are plenty of other fields in this table, but I’ve removed them for the sake of this post. They all permit NULL values.

In my opinion, the problem with this style design is that “I can’t always depend on my queries to produce good results“. Most of the time the system has entered in FIRST_NAME and LAST_NAME into the table and sometimes but not always, they entered a MIDDLE_INITIAL.

So, if we have two users in the system and the data looks like this:

Example: Sample data.

  1. /*
  2. SECURITY_USER_ID  |  USER_NAME  |  LAST_NAME  |  FIRST_NAME  |  MIDDLE_INITIAL
  3. 1                    admin         Master        Web            (NULL)
  4. 2                    jdoe          Doe           John            B
  5. */

…and one of them has a middle initial, while the other does not. Then this creates the potential for unpredictable results in advanced queries.

Example: SQL Statement.

  1. SELECT concat(LAST_NAME, ‘, ‘, FIRST_NAME, ‘ ‘, MIDDLE_INITIAL) AS FULL_NAME
  2. FROM security_users WHERE security_user_id = 1 OR security_user_id = 2

All I want to do is concatenate the users name in SQL, so it displays in the format of my choice without my having to do it in code. Sometimes, it’s nice to bring back data in the format you want. Besides, if I execute this query in more than one place in my application I don’t want to have to continue formatting it in code. I just like to have the query do it.

The problem is, when NULL fields are allowed, you cannot depend on good results. Especially if I built my query and was testing it with people who had middle names. The first time the query came across someone who had a NULL middle name in the application, the application would receive unexpected results.

Example: Query Results

  1. /*
  2. FULL_NAME
  3. (NULL)
  4. Doe, John B
  5. */

Despite the fact there is data in FIRST_NAME (= Web) and LAST_NAME (= Master), the returned result is still NULL. The MySQL concat() function failed to return the proper results, because MIDDLE_INITIAL contains a NULL value.

Using simple straight forward SQL statements failed, due to a NULL field in MIDDLE_INITIAL. This is a visual example; something we can see in a result window. However, if I had a more complicated query and was using a NULL field in a JOIN, then the problem isn’t as interesting. The problem can even become frustrating.

Example: NOT NULL table redesign (following the data structure)

  1. CREATE TABLE security_users (
  2.      id bigint(20) NOT NULL AUTO_INCREMENT,
  3.      user_name varchar(30) NOT NULL,
  4.      user_first_name varchar(20) NOT NULL,
  5.      user_last_name varchar(20) NOT NULL,
  6.      user_middle_initial varchar(1) NOT NULL DEFAULT ”,
  7.  
  8.      UNIQUE KEY user_name (user_name),
  9.      PRIMARY KEY (id)
  10. ) TYPE=InnoDB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1000;

In this table, MIDDLE_INITIAL has a value, even if the value is empty. An empty value is not the same as NULL and the results would return as I might expect them. The query in this post would execute properly and return the right results.

Filed under MySQL Developer · Tagged with , ,

Working with MySQL Auto Incrementing Keys

Posted by Michael on May 10, 2009 · Leave a Comment 

If you’ve been working with Firebird, Oracle, or any number of other databases, the first thing you’ll want to understand is how to work with MySQL auto_increment keys. Anytime you work with keys that are auto incremented by the database engine, you will need a way to retrieve the key values for your insert statements. Relational database are built on joins and understanding how to assign your foreign keys is essential.

Using MySQL to manage your foreign key inserts you can use the LAST_INSERT_ID() function. This allows you to directly insert the value for your foreign keys without having to execute a query or some other mechanism. The LAST_INSERT_ID() MySQL function returns the value of the PRIMARY KEY previously inserted.

This works perfectly for simple designs where only a single foreign key exists and you want to link to the master table.

Example: Tables used

  1. CREATE TABLE web_template (
  2.         id bigint(20) NOT NULL AUTO_INCREMENT,
  3.         template_name varchar(30) NOT NULL,
  4.         template_location varchar(100) NOT NULL,
  5.  
  6.         UNIQUE KEY template_name (template_name),
  7.   PRIMARY KEY (id)
  8. ) TYPE=InnoDB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1000;
  9.  
  10.  
  11. CREATE TABLE web_config (
  12.         id bigint(20) NOT NULL AUTO_INCREMENT,
  13.         config_template_id bigint(20) NOT NULL,
  14.  
  15.   PRIMARY KEY (id),
  16.   FOREIGN KEY (config_template_id) REFERENCES web_template(id) ON DELETE CASCADE
  17. ) TYPE=InnoDB DEFAULT CHARSET=UTF8 AUTO_INCREMENT=1000;



Example: Insert statements

  1. INSERT INTO web_template (template_name, template_location) VALUES (‘Default Template’, ‘/template/Default/’);
  2.  
  3. INSERT INTO web_config (config_template_id) VALUES (LAST_INSERT_ID());



In PHP we have the mysql_insert_id() function, which allows us to retrieve the last key inserted. This function works pretty good, but it must be executed before you commit your transaction, otherwise you might get unexpected results. Additionally, it’s ideal to execute the statement directly after you submit your insert statement.

Using this function gives us more flexibility in key management in our applications, especially when working with tables that have multiple foreign keys.

Example: PHP statements

  1. <?php
  2. mysql_query("insert into web_template (template_name, template_location) values (‘Default Template’, ‘/template/Default/’");
  3. $web_template_id = mysql_insert_id();
  4. mysql_query("insert into web_config (config_template_id) values (".$web_template_id.")");
  5. ?>



Note: In a “data aware controls” environment, it’s not always easy to manage your auto incrementing keys (a lot of this is due to design, not just environment). In most cases where I have the decision making power I try to avoid data aware control development (especially on the web, now that Delphi for PHP is available). Read only grids are an exception, but most of the time you can design your applications to avoid “live data aware” entry/edit forms. It may seem like more work, but I believe in the end it’s cleaner and easier to debug.

Filed under MySQL Developer, PHP Developer · Tagged with ,

webJestic.NET