How to Utilise and Apply Access Database Normalisation Techniques

By Ben S Beitler

The process of Database Normalisation was developed by E.F.Codd who is widely considered the father of relational database theory.

There are several rules which provide theoretical structures and disciplines which are not always practical to follow but help provide the main goals which are:

1. Eliminate redundant information
2. Increase data integrity
3. Make systems ore efficient

Modern databases should be in BCNF Boyce-Codd Normal Form which is deemed to be at third normal form of which there are considered to be five in all. This article focuses on what I believe is considered a good balance to applying some of these rules and covers up to the third norm of database normalisation.

Before the First Normal Form refers to several tables managed into smaller units that have a potential relationship and have the following attributes:

1. Each table is described as one entity so that an example of an 'Order Processing' database may have several tables which would be divided into logical units (tables) including Customers and Orders and not stored in one complete table (known as a flat-file).
2. Each record in a table is not duplicated so that a field in a record can be set as unique - primary key.
3. The fields (columns) are in no particular order.
4. The records (rows) are in no particular order.

The key is the key and each table should have a primary key set though this is optional in Access it is highly recommended that you at least set this type of key for each table you intend to relate.

First Normal Form (1NF) says that all column values must be atomic. Atomic means indivisible and refers to row-by-column position where you should have multiple records (or repeating groups) and not all values stored in one record. For example, an order raised for a customer may contain one or more items. You shouldn't store the order in one row for multiple items as you would have a difficult time retrieving information from the record.

By storing each item in a separate record with a common link to the ID field follows the principle of the First Normal (INF) of database normalisation. Even if there is no unique ID in the illustration above, you can either add a unique audit trail ID field and set the primary key or consider the composite of the two fields which will make it unique too i.e. combining the 'Order ID' and 'Order Item Id' fields. This will help with writing queries later on where you can filter by an item and more importantly calculate with the 'Quantity' field.

Second Normal Form (2NF) This follows on from the First Normal (1NF) level where by tables should store data relating to only one thing (entity) and that this entity should be fully described by it's primary key.

For example, an Orders database system may contain the following four tables:

1. Customers which has a primary key (Customer ID) and the data relates to the customer profile namely name, address and contact details.
2. Orders which holds the order header information including order reference (as the primary key), date and shipping information but not the details for each item.
3. Order Details which has a secondary key (Order ID as defined from the Orders table) and the data relates to the main order header showing the items of the order namely product, price and quantity.
4. Products which has a primary key (Product ID) and the data relates to the product profile namely name, price and stock levels.

RSS Feed

 Follow @Stephen Bucaro

 Active Server Pages
 Java
 MySQL
 Visual Basic Programming
 XML
 Microsoft TechNet Home