Database Normalization: First, Second, and Third Normal Forms
I read a great explanation of first, second, and third normal form a few weeks ago. For those that know what database normalization is but haven't seen the "forms", the different forms are essentially rules for having a well normalized relation DB. Keeping them in mind when doing DB design is key to keeping a great database. I'd like to make an attempt at condensing the linked tutorial into its essentials.
First Normal Form (1NF): No repeating elements or groups of elements
Don't repeat your columns. Avoid this:
| OrderId | ItemId1 | ItemId2 | … |
| 1 | 100 | 101 |
ItemId1, 2, ... should be split out into relational tables.
Second Normal Form (2NF): No partial dependencies on a concatenated key
This is a complex way of saying that if a column isn’t intrinsically related to the entire primary key, then you should break out the primary key into different tables.
Example:
| OrderId (PK) | ItemId (PK) | OrderDate | … |
| 1 | 100 | 2009-01-01 | |
| 1 | 101 | 2009-01-01 |
The primary key is (OrderId, ItemId).
Consider OrderDate. It is conceptually part of an order. An order always occurs at some time. But is an OrderDate related to an Item? Not really.
You may be saying, “but items are part of an order!”, and you would be right. But that’s not what I’m getting at. OrderDate is independent of the item itself.
Look at another way: in the table above the OrderDate will always be the same for a given OrderId regardless of the value of the ItemId column. This means data duplication, which is denormalization.
Here’s how we correct the problem:
| Orders | ||
| OrderId (PK) | OrderDate | … |
| 1 | 2009-01-01 |
| Order_Items | ||
| OrderId (PK) | ItemId (PK) | … |
| 1 | 100 | |
| 1 | 101 |
Here is an excellent line from the article, “All we are trying to establish here is whether a particular order on a particular date relies on a particular item.”
Third Normal Form (3NF): No dependencies on non-key attributes
2NF covers the case of multi-column primary keys. 3NF is meant to cover single column keys. Simply stated, pull out columns that don’t directly relate to the subject of the row (the primary key), and put them in their own table.
Example:
| Orders | |||
| OrderId (PK) | OrderDate | CustomerName | CustomerCity |
| 1 | 2009-01-01 | John Smith | Chicago |
Customer information could be the subject of its own table. Pull out customer name and other customer fields into another table, and then put a Customer foreign key into Orders.
Wikipedia has a great quote from Bill Kent: “every non-key attribute ‘must provide a fact about the key, the whole key, and nothing but the key’.”
On Database Abstraction, PHP, and Ruby
It took me a couple days on my current PHP/MySQL project to get the DB abstraction with proper error handling, input validation, and relational support coded to the point where I'm happy with the model. This was after trying Zend_Db, Doctrine, and Propel, which are all good libraries, but I hit points where the work of getting it to do what I wanted efficiently just wasn't worth it. I decided it would be faster to just roll my own slim library.
I'm always mystified at why DB models are such a hassle to code, so I spent a little time reading. I think I get it now. It has to do with the mapping problem between relational systems and object oriented systems.
Ted Neward, writer of several books on C# and Java, calls this "The Vietnam of Computer Science". What he's saying is that object-relational mapping (ORM) quickly reaches a point of diminishing returns. The problem stems from the conceptual disconnect between the language and the data store.
Hoping to shed additional light on the subject, I went looking for alternative perspectives to my PHP/MySQL solution. Since it's all the rage for ease of use, I was mostly curious about Ruby, the Rails framework, and object oriented databases.
Ruby is certainly different, but after some reading, I'm starting to understand why the language lends itself better to the OO style of DB abstraction. On top of that, Rails has powerful scaffolding that lets you flesh out pieces very fast without the code getting ugly. With ActiveRecord in mind from the ground up, things can be pretty elegant some times.
I also found some really cool systems for bridging the gap between languages and data stores. For instance, check out MagLev for Smalltalk/Ruby. Imagine using an OO DB so you don't have to do the relational mapping, doing it in a distributed fashion, and using things like shared memory more efficiently as an object staging area between your running site scripts and the DB. That's kind of MagLev.
All of this is interesting, but for the current project I'm sticking with PHP. However, on the next project I might try Ruby on Rails. I want to see first hand if and how it overcomes some of the challenges.
On a slightly tangential subject, a part of me really dislikes the weak typing of both Ruby and PHP. The net effect in PHP is that you pretty much end up using arrays for everything, making it hard to keep track of structure. Ruby on Rails does get around some of the weak typing issues with some better built-in validation, scaffolding, and member attributes (I'm not sure if that's what they call them). Sometimes I'd rather be doing C#.