Access Tips & Rules
Normalizing Your DatabaseWhen a database suffers from poor table organization, experts say it's not normalized.
Here are some Access tips on rules governing how a relational database should store its tables (These are the rules of data normalization.)
Five normalization rules exist, but the last three are fairly complicated and used mostly by database professionals.
On this page, I'll explain the first two normalization rules, which are all a beginner really needs to understand to avoid major mistakes, again some Access tips worth mentioning.
RULE 1: Avoid Repeated InformationSuppose you want to keep contact information on your customers along with a record of each transaction they make.
If you kept it all in one table, you would have to repeat the customer's full name, address, and phone number each time you entered a new transaction. It would also be a nightmare if the customer's address changed where you would have to change the address in every transaction record for that customer.
Wrong way to do it...
<![endif]> A better way is to break the table up into two separate tables, and then assign each customer an ID Number.
Include that ID Number in a table that contains names and addresses. Then use the same ID Number as a link in a separate table that contains the transactions, as show below.
The right way to do it...
RULE 2: Avoid redundant dataSuppose you want to keep track of which employees attended certain training classes. Many employees and lots of classes are involved. One way would be to keep it all in a single Personnel table, such as this:
(Wrong way to do it...)
But what if an employee takes more than one class?
You'd have to add a duplicate line in the table to list it, and then you have the problem described in the previous section — multiple records with virtually identical field name entries.
What if the only employee who has taken a certain class leaves the company?
When you delete that employee's record, you delete the information about the class's credit hours, too.
A better way would be to create separate tables for Employees, Classes and Training Done, as shown in the following example:
(The right way to do it...)
Access XP Topics- Screen layout
- Storing data
- Database planning
- Tips for tables
- Tips for forms
- Tips for reports