Contents

THIS DOCUMENT IS STILL UNDER DEVELOPMENT
⇑ TOP

Database Design

Introduction

I'm not going to try and condense in a few pages, what takes months to learn and years to apply. This is the conundrum with Database Design, you can't simply read a book and then become proficient.

Third Normal Form (3NF)

Third Normal Form (3NF) an fifth Normal Form (5NF)

Normalise

Optimise

Part of normalisation to a third normal form is the need to break these rules sometimes. Some call this de-normalisation. I prefer to use the word Optimise, because the purpose of breaking third normal form should only be to optimise the design for end user operation. Here is an example, it's not an ideal one, but it clearly highlights optimisation. You have a Customer and a CustomerOrder table. The CustomerOrder records the Date of the Order, and the total amount. However in this sales driven environment, we want to closely monitor Customers that have not made recent orders, or have made only small orders. 
  SELECT c.name, SUM(co.total) AS total, 
         TO_DAYS(CURDATE()) - TO_DAYS(co.orderDate) AS days
  FROM   Customer c, CustomerOrder co
  WHERE  c.customerId = co.customerId
For each user (sales rep) of the system, let's say there are 10 concurrent users, and every few minutes they re-run this query. Now it's quite possible that the system will perform adequately and return results within a few seconds, however if this example. So the solution is to optimise the information for the Customer that can be determined from the CustomerOrder. The cost is low, for each Insert into a CustomerOrder, we now need an update of Customer. Within MySQL for example, this can even be achieved via a Database Trigger, again abstracting this from the application. 
  UPDATE Customer
  SET    orderTotal    = orderTotal + :newTotal,
         lastOrderDate = :orderDate
  WHERE  customerId    = :customerId

  SELECT c.name, c.orderTotal, TO_DAYS(CURDATE()) - TO_DAYS(c.lastOrderDate) 
  FROM   Customer c
⇑ TOP