In planning and designing a Microsoft Access database system, it is important to apply the principles of normalization to avoid duplication of data and to preserve data integrity. You should consider carefully which fields should be grouped together to form a table and ensure that all fields in a table do indeed belong together.
Let us take the example of a database containing information about clients and also need to store information about contacts within the organisations, you may be tempted to place all of the information in a single table. However, this would prove limiting, since entering more than one contact for the same company would require duplication. If you create a record for each of the contacts for a given company, several of the fields would contain the same values.
The first level of normalization involves removing the possibility of duplication by splitting the data into different tables. In the foregoing example, you would split the data into two tables: one containing company information and one containing contact information. Since the information in both tables is related, they would be given a common field (a client ID) which would then be used to link the two tables. Thus, if you have five different contacts for a given company, you would only enter the company information once, in the company table and, each time you create a contact for a company, you would only need to enter the client ID in the contact table.
Splitting different data into different tables and then creating relationships between them offers many benefits. Firstly, it avoids duplication: thus, in our example, the company information is only stored once and will therefore be smaller in size. If company information needs to be updated, it only needs to be done once.
Another important part of table design is to avoid redundant data. This often means avoiding the storage of information which can easily be calculated. Thus, if you are storing information about staff, you do not need to both the date of birth and the age of an individual, since the age can be calculated from the data of birth.
Let us take the example of a database containing information about clients and also need to store information about contacts within the organisations, you may be tempted to place all of the information in a single table. However, this would prove limiting, since entering more than one contact for the same company would require duplication. If you create a record for each of the contacts for a given company, several of the fields would contain the same values.
The first level of normalization involves removing the possibility of duplication by splitting the data into different tables. In the foregoing example, you would split the data into two tables: one containing company information and one containing contact information. Since the information in both tables is related, they would be given a common field (a client ID) which would then be used to link the two tables. Thus, if you have five different contacts for a given company, you would only enter the company information once, in the company table and, each time you create a contact for a company, you would only need to enter the client ID in the contact table.
Splitting different data into different tables and then creating relationships between them offers many benefits. Firstly, it avoids duplication: thus, in our example, the company information is only stored once and will therefore be smaller in size. If company information needs to be updated, it only needs to be done once.
Another important part of table design is to avoid redundant data. This often means avoiding the storage of information which can easily be calculated. Thus, if you are storing information about staff, you do not need to both the date of birth and the age of an individual, since the age can be calculated from the data of birth.
About the Author:
To learn more about Access training courses, visit Macresource Computer Training, a UK IT training company offering Access Classes in London and throughout the UK.
No comments:
Post a Comment