Determining What Tables You'll Need

How many tables will you need? Technically, you will only need one. That's the minimum a database can function with. However, the biggest mistake most people make with Access is to put too much information in one table. Access is a relational database program; unlike more simple database programs, it's meant to handle lots of tables and create relationships among them. Figure 2.1 shows a list of tables in a database that keeps track of the employee hours spent on various projects.

Another big mistake people make is to try to make each table look like a stand-alone report. For instance, they might repeat a customer's name and address in all eight tables because they want that information readily available when it's required. This is a waste! You can easily create a report or form that includes this information whenever you need it; it needs to appear in only one table.

Normalizing Your Database

When a database suffers from poor table organization, experts say it's not normalized. There are rules that govern how a relational database should store its tables; these are the rules of Data Normalization.

There are five normalization rules, but the latter ones are fairly complicated and used mostly by database professionals. In this lesson, I'll explain the first two normalization rules, which are all a beginner really needs to understand in order to avoid major mistakes.

1. Avoid Repeated Information

Let's say that 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; you would have to record change every transaction.

Customer Name	Customer Address	Customer Phone	Order Date	Order Total
ABC Plumbing	201 W. 44th St.	(317) 555-2394	2/5/96	$155.90
ABC Plumbing	201 W. 44th St.	(317) 555-2394	5/14/96	$90.24
ABC Plumbing	201 W. 44th St.	(317) 555-2394	7/9/96	$224.50
Jack's Emporium	1155 Conner Ave.	(317) 555-4501	6/6/95	$1,592.99
Jack's Emporium	1155 Conner Ave.	(317) 555-4501	7/26/96	$990.41
Millie's Pizza	108 Ponting St.	(317) 554-2349	8/29/96	$39.95

A better way is to assign each customer an ID number. Include that ID number in a table that contains names and addresses; then include the same ID number as a link in a separate table that contains transactions.

Table . Customers Table

Customer ID	Customer Name	Customer Address	Customer Phone
1	ABC Plumbing	201 W. 44th St.	(317) 555-2394
2	Jack's Emporium	1155 Conner Ave.	(317) 555-4501
3	Millie's Pizza	108 Ponting St.	(317) 554-2349

Table . Orders Table

Customer ID	Order Date	Order Total
1	2/5/96	$155.90
1	5/14/96	$90.24
1	7/9/96	$224.50
2	6/6/95	$1,592.99
2	7/26/96	$990.41
3	8/29/96	$39.95

2. Avoid Redundant Data

Let's say you want to keep track of which employees have attended certain training classes. There are lots of employees and lots of classes. One way would be to keep it all in a single Personnel table, like this:

Employee Name	Employee Address	Employee Phone	Training Date	Class Taken	Credit Hours	Passed?
Phil Sharp	211 W. 16th St.	(317) 555-4321	5/5/96	Leadership Skills	3	Yes
Becky Rowan	40 Westfuekd Ct.	(317) 555-3905	5/5/96	Customer Service	2	Yes
Nick Gianti	559 Ponting St.	(317) 555-7683	6/15/96	Public Speaking	9	Yes
Martha Donato	720 E. Warren	(317) 555-2930	5/5/96	Public Speaking	9	No
Cynthia Hedges	108 Carroll St.	(317) 555-5990	6/15/96	Customer Service	2	Yes
Andrea Mayfair	3904 110th St.	(317) 555-0293	6/15/96	Leadership Skills	3	Yes

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 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, like so:

Table . Employee Table

Employee ID	Employee Name	Employee Address	Employee Phone
1	Phil Sharp	211 W. 16th St.	(317) 555-4321
2	Becky Rowan	40 Westfield Ct.	(317) 555-3905
3	Nick Gianti	559 Ponting St.	(317) 555-7683
4	Martha Donato	720 E. Warren	(317) 555-2930
5	Cynthia Hedges	108 Carroll St.	(317) 555-5990
6	Andrea Mayfair	3904 110th St.	(317) 555-0293

Table . Class Table

Class ID	Class	Credits
C1	Leadership Skills	3
C2	Customer Service	2
C3	Public Speaking	9

Table . Training Table

Employee ID	Date	Class	Passed?
1	5/5/96	C1	Yes
2	5/5/96	C2	Yes
3	6/16/96	C3	Yes
4	5/5/96	C3	No
5	6/15/96	C2	Yes
6	6/15/96	C1	Yes

Summary: Designing Your Tables

Don't be overwhelmed by all this information about database normalization; good table organization boils down to a few simple principles:

• Each table should have a theme; for instance, Employee Contact Information or Customer Transactions. Don't try to have more than one theme per table.

• If you see that you might end up repeating data in a table in the future, plan now to split the information that will be repeated into its own table.

• If there is a list of reference information you want to preserve (such as the names and credit hours for classes), put it in its own table.

• Wherever possible, use ID numbers, as they'll help you link tables together later and help you avoid typing errors that come from entering long text strings (such as names) over and over.