How to Utilise and Apply Access Database Normalisation Techniques
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
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.