Feb 09, 2015

In our final part of the three part series about normalising databses (for part 1, click here and for part 2, click here), we satisfy all requirements for 3NF, or the Third Normal Form.  There are two basic requirements for a database to be in third normal form:

• Already meet the requirements of both 1NF and 2NF
• Remove columns that are not fully dependent upon the primary key.

Imagine that we have a table of widget orders that contains the following attributes:

• Order Number
• Customer Number
• Unit Price
• Quantity
• Total

Remember, our first requirement is that the table must satisfy the requirements of 1NF and 2NF. Are there any duplicative columns? No. Do we have a primary key? Yes, the order number. Therefore, we satisfy the requirements of 1NF. Are there any subsets of data that apply to multiple rows? No, so we also satisfy the requirements of 2NF. Now, are all of the columns fully dependent upon the primary key? The customer number varies with the order number and it doesn't appear to depend upon any of the other fields. What about the unit price? This field could be dependent upon the customer number in a situation where we charged each customer a set price. However, looking at the data above, it appears we sometimes charge the same customer different prices. Therefore, the unit price is fully dependent upon the order number. The quantity of items also varies from order to order, so we're OK there. What about the total? It looks like we might be in trouble here. The total can be derived by multiplying the unit price by the quantity, therefore it's not fully dependent upon the primary key. We must remove it from the table to comply with the third normal form. Perhaps, we use the following attributes:

• Order Number
• Customer Number
• Unit Price
• Quantity

Now, our table is in 3NF. But, you might ask, what about the total? This is a derived field and it's best not to store it in the database at all. We can simply compute it "on the fly" when performing database queries. For example, we might have previously used this query to retrieve order numbers and totals:

SELECT OrderNumber, Total FROM WidgetOrders

We can now use the following query to achieve the same results without violating normalisation rules:

SELECT OrderNumber, UnitPrice * Quantity AS Total FROM WidgetOrders

Job jobbed!