Database Reference
In-Depth Information
Database design
In a well-designed database, each item of data is stored in only one table. If you're
capturing 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.
2
For example, an Orders table should not include information about the customer plac-
ing 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 Orders 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, know-
ing 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 customer's name and address from the table, because
more than one customer might have the last name Jones.
A detailed discussion of normalization processes is beyond the scope of this topic. For
more information, see Access Help, or search for
Database design basics
on the Office
website.
