98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Access articles
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)
Author is a freelance copywriter. For more information on access+training, please visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-364-ms-access-database-normalisation.html
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsCrown Prosecution Service
Casework Asst Anjna Chapanairy Taking Minutes This is the first STL course I have attended and I enjoyed it. It was engaging and more enjoyable then most courses I have been on and cannot think of any suggestions. Casio Electronics Company Ltd
Senior Sales Administrator Minal Joshi Excel Intermediate I feel I have really advanced my knowledge on Excel a step forward. Shortcuts like 'alt=' to sum up the data, and using the different tabs on Excel paste special, data validation as well as the IF functionality which will be very beneficial in my work. HG Capital
Accounts Assistant Jenny Tedeschi Excel Intermediate I found the course very useful and I will be using a lot of it at work, the teacher went above and behind in explaining the course and extra features that weren't included but will be very useful to me |
PUBLICATION GUIDELINES