Access Tips & Rules
 |
 |
 |
 |
 |
 |
 |
Access Tips
Additional Resources
Access Tips
Access Tips
Access Tips & Rules | Microsoft Access XP |
 |
 |
What's on this page... Normalizing you database RULE 1: Avoid repeated information RULE 2: Avoid redundant data |
Normalizing Your Database
When 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.)
|
|
Data Normalization makes
the tables as efficient and compact as possible to eliminate the possibility for confusion and error. |
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.
|
|
Access Tips: Normalized Wizards Luckily, database professionals had a hand in creating Access's Database Wizard, so any tables you create with this feature will be normalized. |
RULE 1: Avoid Repeated Information
Suppose 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...
| Customer Name | Customer Address | Customer Phone | Order Date | Order Total |
| ABD Plumbing | 201 W. 44th St. | (321) 555-6656 | 2/5/96 | $ 155.23 |
| ABD Plumbing | 201 W. 44th St. | (321) 555-6656 | 5/14/96 | $ 90.25 |
| ABD Plumbing | 201 W. 44th St. | (321) 555-6656 | 7/9/96 | $ 225.20 |
| Jack's Emporium | 1155 Conner Ave. | (356) 996-6621 | 6/6/96 | $ 1,532.00 |
| Jack's Emporium | 1155 Conner Ave. | (356) 996-6621 | 7/26/96 | $ 996.95 |
| Millie's Pizza | 108 Pond St. | (145) 653-6969 | 8/29/96 | $ 39.23 |
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...
Customers Table
| Customer ID | Customer Name | Customer Address | Customer Phone |
| 1 | ABD Plumbing | 201 W. 44th St. | (321) 555-6656 |
| 2 | Jack's Emporium | 1155 Conner Ave. | (356) 996-6621 |
| 3 | Millie's Pizza | 108 Pond St. | (145) 653-6969 |
Orders Table
| Order ID | Customer ID | Order Date | Order Total |
| 1 | 1 | 2/5/96 | $ 155.23 |
| 2 | 1 | 5/14/96 | $ 90.25 |
| 3 | 1 | 7/9/96 | $ 225.20 |
| 4 | 2 | 6/6/96 | $ 1,532.00 |
| 5 | 2 | 7/26/96 | $ 996.95 |
| 6 | 3 | 8/29/96 | $ 39.23 |
RULE 2: Avoid redundant data
Suppose 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...)
| Employee Name | Employee Address | Employee Phone | Training Date | Class Taken | Credit Hours | Passed? |
| Phil Sharp | 211 W. 16th St. | (312) 555-4588 | 5/5/96 | Leadership Skills | 3 |
 |
| Becky Rowan | 40 Westfield Rd. | (312) 554-6895 | 5/5/96 | Customer Services | 2 |
|
| Nick Gianti | 559 Ponting St. | (312) 555-6665 | 6/15/96 | Public Speaking | 9 |
|
| Martha Donato | 720 E. Warren | (312) 545 6556 | 5/5/96 | Public Speaking | 9 |
|
| Cynthia Hedges | 108 Carol St. | (312) 545-6456 | 6/15/96 | Customer Services | 2 |
|
| Andrea Mayfair | 3095 110th St. | (312) 525-8832 | 6/15/96 | Leadership Skills | 3 |
|
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...)
Employees Table
| Employee ID | Employee Name | Employee Address | Employee Phone |
| 1 | Phil Sharp | 211 W. 16th St. | (312) 555-4588 |
| 2 | Becky Rowan | 40 Westfield Rd. | (312) 554-6895 |
| 3 | Nick Gianti | 559 Ponting St. | (312) 555-6665 |
| 4 | Martha Donato | 720 E. Warren | (312) 545 6556 |
| 5 | Cynthia Hedges | 108 Carol St. | (312) 545-6456 |
| 6 | Andrea Mayfair | 3095 110th St. | (312) 525-8832 |
Class Table
| Class ID | Classes | Credits |
| C1 | Leadership Skills | 3 |
| C2 | Customer Services | 2 |
| C3 | Public Speaking | 9 |
 |
| Back to Top |
| See also... Database Planning |
| Return to Microsoft Access Help from Access Tips & Rules |
Access XP Topics
- Screen layout- Toolbars
- Storing data
- Database planning
- Tips for tables
- Tips for forms
- Tips for reports