Microsoft Office Tutorials and References
In Depth Information
Designing a Database
Eliminating redundant fields
Look over the fields you identified, and make sure they’re all actually needed
for your application. Is each piece of information something that may appear
on a form or report later or that may be needed to calculate something? If
not, throw it out.
In this case, it’s worth repeating: Don’t store the same information in more than
one place. In a database, redundant information makes double the work when
you’re updating the information. Instead, figure out the right place to store
the information, and store it there — once. If you can calculate one field from
another field, store only one. Storing both age and birth date, for example, is
pointless, because a person’s age changes, but the birth date doesn’t. Store
the birth date; you can always get Access to do the math for you.
The same is true of information that you can look up. For codes of all types
(state and province codes, product codes, and the like), make a table for the
codes that includes a field for each code and a field for the code’s meaning.
Then all the other tables in your database store only the code, and Access
looks up the code’s meaning when you need it to appear in a form or report.
For the online bookstore, you don’t need to store the title and author of each
item that a customer buys; instead, you can just store the book’s ISBN (the
unique number assigned to each book).
On the other hand, you can’t always avoid redundancy. An item of
information may change in one place but not in another, so you may have to store it
in more than one place. In the bookstore system, when the price of a book
changes, the amount that previous customers paid for the book doesn’t
change. In addition to storing the book’s current selling price, you may want
to store the book’s price in the record for each sale.
Organizing fields into tables
Okay, you have a bunch of fields. Are they all in one table, or should you set
up multiple tables?
One way to tell whether your system needs multiple tables is to check
whether you have different numbers of values for different fields. Suppose
that the store carries 2,000 products, and you have about 16,000
customers. You have 2,000 different names, prices, and descriptions, while you
have 16,000 customer names, addresses, and sets of credit-card
information. Guess what? You have two different tables: a Products table with 2,000
records and a Customers table with 16,000 records.