Normalize Table Design
In Microsoft Access, to normalize your table design means to take any items in your database table that are duplicated and move the duplicated items in a new table to create a relationship between the original table and the new table.
For a layperson, normalizing may mean turning a complicated item into a regular or normal one. However in Microsoft Access, normalization is the process of effectively organizing data in your database. The main objective of normalization is to remove all redundant items in a single data. That way, it will lessen duplication and your data will be more reliable. It is said that your data is redundant if you stored the same data in one and the same table. Normalizing data will save you time in sorting out same data and it will make your data more trustworthy.
The database shows two tables, “Customer” and “Products”. Both contain I.D field, Name field, and a Date and Time field. To avoid redundancy, they should be combined through normalizing.
Creating Relationships between Tables
In Microsoft Access, you can normalize your tables by creating a relationship between them.
One of the great things in Microsoft Access is its ability to maintain relationships between different tables. It makes it possible to correlate data and to ensure its consistency of each data in every table.
Relationships are made by matching your data in key fields which are usually fields with the same name in both tables.
There are three types of relationship between tables. These are:
1. One-to-Many relationship: A type of relationship wherein a row in table A can have many similar rows in table B, but a row in table B has only one similar row in table A. Use one-to-many relationship if only one of the related columns is a primary key. A one-to-many relationship is the most common type of relationship
2. Many-to-Many relationship: A type of relationship in which a row in table A can have many similar rows in table B, and vice versa.
3. One-to-One relationship: As the name suggests, a row in table A only has one similar row in table B, and vice versa. Using one-to-one relationship is possible if:
- The user wishes to divide a table with many columns
- The user wishes to isolate part of a table for security reasons
- The user wishes to store data that is short-lived and is easily deleted
- The user wishes to store information that applies only to a subset of main table.
To create a relationship:
Step 1. Go to the “Database Tools” and select “Relationships”
Step 2. Microsoft Access will then lead you to a new window named “Show Table”. Inside the window are the tables and their respective fields. Choose and add the tables you wish to make a relationship with.
Step 3. Once you have chosen your tables, it will look something like this
Step 4. Click and drag the field that both tables have in common. In my example, I.D is similar with both tables. Their relationship type is indicated below the window
Step 5. Once you click “Create”, a line will form to show the relationship between the two tables.
Editing and Deleting Table Relationships
If you wish to edit your table relationship, here is what you need to do:
Step 1. Click “Edit Relationship” located at the upper left side of your screen.
Step 2. A window will appear in which you can edit your relationship
Step 3. Make the changes that need to be done and select “OK”
Step 4. You can also create a new table relationship
1. Click “Edit Relationship” located at the upper left side of your screen.
2. Select “Create New”
3. Select the table and field name you wish to create a relationship with then click “OK”
If you wish to delete the relationship of your tables:
1. Click “Clear Layout”
2. Select “OK”
Your relationship between tables is now deleted.