« Windows XP on Apple Hardware in the Wild | Main | JIT MySQL Session Coverage »

April 25, 2006

Database Normalization and Joins

First session of Tuesday morning at the MySQL Users Conference (2006) is Mike Hillyer and Arjen Lenz presenting An Introduction to Database Normalization and Joins.

Not a new subject for me, but I've heard good things about this presentation (has been given at a number of conferences) so want to see what it's about.

What is the overall idea with database normalization?

Presentation will cover the first three forms of normalization. Mike shows an example of a book table with lots of data piled up into single column rows.

First Normal Form

Solving it means breaking data into separate tables.

Forming Relationships

There are three forms of relationships:

Second Normal Form

Data must be in first normal form first. Second normal is about composite keys. All columns in a row must refer to the entire primary key.

Third Normal Form

All columns must depend directly on the primary key. "The key, only the key, and nothing but the key, so help me Codd." An example is having a an author table with address, city and zip. The zip isn't necessarily part of the user, it belongs to a city. Mike points out that to get this form of normalization with the zip code example requires many tables, often more than is really required.

(Arjen takes over for the second part of the talk on JOINs)

A JOIN is really about sets (Venn diagrams). Arjen shows a few diagrams with geometric shapes surrounding groups of numers to demonstrate the data that will be returned in the resultset.

Inner gets the sets of common data between two tables. Outer gets the common values plus the extras in the right or left depending on what you specify.

Presentation will be on Mike's site.

Posted by mike at April 25, 2006 1:55 PM