804 Chapter 26 Create Databases and Simple Tables
Database Design
In a well-designed database, each item of data is stored only once. If you’re cap-
turing the same information in multiple places, that is a sure sign that you need
to analyze the data and figure out a way to put the duplicated information in a
separate table.
For example, an Orders table should not include information about the customer
placing each order, for two significant reasons. First, if the same customer orders
more than once, all his or her information has to be repeated for each order, which
inflates the size of the table and the database. Second, if the customer moves, his
or her address will need to be updated in the record for every order placed.
The way to avoid this type of problem is to put customer information in a Customers
table and assign each customer a unique identifier, such as a sequential number or
unique string of letters, in the primary key field. Then in the Orders table, you can
identify the customer by the unique ID. If you need to know the name and address
of the customer who placed a particular order, you can have Access use the unique
ID to look up that information in the Customers table.
The process of ensuring that a set of information is stored in only one place is
called normalization. This process tests a database for compliance with a set of
normalization rules that ask questions such as “If I know the information in the
primary key field of a record, can I retrieve information from one and only one
record?” For example, knowing that a customer’s ID is 1002 means you can pull
the customer’s name and address from the Customers table, whereas knowing
that a customer’s last name is Jones does not mean that you can pull the cus-
tomer’s name and address from the table, because more than one customer
might have the last name Jones.
The topic of normalization is beyond the scope of this book. If you need to design
a database that will contain several tables, you should search for Database
design basics in Access Help to learn more about the normalization process.