MS Access and Database Normalisation
Tue 21st October 2008
Whether you are new to databases or have been creating and working with them for some time, you are bound to have heard the term 'normalisation'. If not, a very brief description of normalisation is: the process of efficiently organising data in a database.
The two predominant purposes of the normalisation process are to eliminate redundant data (i.e. to remove replicated data) and to ensure that data dependencies make sense (i.e. only related data is stored in a table) and, in so doing, to safeguard the database against certain types of logical or structural problems.
Both of these purposes are valid as they reduce the amount of storage space required to maintain the database and ensure that the data is logically stored. When multiple instances of a given piece of information occur in a table, the possibility that these instances will not be kept consistent when the data within the table is updated is avoided, therefore avoiding a loss of data integrity.
Normalisation is often considered to be a luxury that only academics and more experienced database creators have time for. This is however not the case, as knowing and implementing the principles of normalisation to your database design tasks really isn't all that complicated and it can drastically improve the performance of your DBMS (Database Management System).
There are five stages of normalisation that have been developed by the database community. They are referred to as normal forms and progress from least restrictive to most restrictive - from First Normal Form (1NF) to Fifth Normal Form (5NF).
Most commonly, database designers and operators do not implement anything higher than 3NF, with the 5NF rarely being seen because higher degrees of normalisation typically require more tables which then create the need for a larger number of 'joins' which can lead to a reduction in the performance of the database.
A brief description of the first three normal forms is as follows:
1NF sets the very basic rules for an organised database:
- Remove duplicate columns from the same table
- Create separate tables for each group of related data
- Identify each row with a unique column or set of columns
2NF assists with the further removal of duplicate data:
- Meet the requirements of 1NF
- Remove subsets of data that apply to multiple rows of a table and place them in separate tables
- Create relationships between these new tables and their predecessors through the use of foreign keys (which can be used to cross-reference tables)
3NF goes a large step further, in that it:
- Meets all the requirements of 2NF
- Removes all columns that are not dependent upon the primary key (used to uniquely identify records in a database. Access can generate a primary key for your database upon request)
Original article appears here: