Microsoft Office Tutorials and References
In Depth Information
Designing a Database
Adding tables for codes and abbreviations
Look at your tables to see whether the fields contain any standard codes,
such as two-letter state and province codes, zip codes, or other codes. The
bookstore’s Customers table includes a State/Province field and a Zip/
Postcode field. The Products table contains a Product Type field so the
store can track sales of books (type B) versus other types of stuff (such as
G for groceries or D for DVDs). Determine whether your system needs to do
one of these tasks with the codes:
Book I
Chapter 3
Validate the codes. Wrong codes cause trouble later. Validating the
codes when you type them is always best. If someone types VR for
Vermont, the post office may not deliver your package. Later, when you
analyze your sales by state, you see that you have some Vermonters
with the right code (VT) and some with the wrong code. (See Book II,
Chapter 5 for more information on how to set up validation in your
database.)
Look up the meaning of the code. Codes usually stand for something.
Should your system print or display the meaning of the code? If you
have a report showing total sales of products by type, printing Books,
Groceries, and Gifts rather than B, G, and G is nice.
If you want to either validate or look up the codes you store, create a
separate table to hold a list of your codes and their meanings. You could add the
following two tables to the bookstore database:
States/Provinces
State or Province Code
State or Province Name
Country
Product Types
Product Type Code
Product Type Description
Although zip codes and postal codes are codes (well, yeah), most databases
don’t include tables that list them. The reason is simple: Pretty soon, your
system would be overstuffed with them. (About 100,000 zip codes exist, for
openers.) Also, you have to update the table constantly as the post offices
issue — and change — zip and postal codes. If you really want to validate
your zip codes, you can get a zip-code database from the U.S. Postal Service
at www.usps.com .
Choosing primary keys for each table
The next step in designing your database is making sure that each table has
its own primary key field(s). Each table needs at least one field that uniquely
identifies each record in the table. (We find that it’s almost always better
to use one field as your primary key.) Look for a field in the table that has a
Search JabSto ::




Custom Search