Microsoft Office Tutorials and References
In Depth Information
Designing a Database
Choosing fields for database tables
As I explain earlier, fields are categories of information. Each database table
needs at least one field. If the table itself is a subject, you could say that its
fields are facts about the subject. An Address database table needs fields
for recording street addresses, cities, states, and ZIP Codes. A Products
database table needs fields for product ID numbers, product names, and unit
prices. Just the facts, ma’am. Within the confines of the subject, the
database table needs one field for each piece of information that is useful to your
When you’re planning which fields to include in a database table, follow
✦ Break up the information into small elements. For example, instead of
a Name field, create a First Name field and a Last Name field. This way,
you can sort database tables by last name more easily.
✦ Give descriptive names to fields so that you know what they are later. A
more descriptive name, such as Serial Number, is clearer than SN.
✦ Think ahead and include a field for each piece of information your
organization needs. Adding a field to a database table late in the game is a
chore. You have to return to each record, look up the information, and
✦ Don’t include information that can be derived from a calculation. As I
explain in Chapter 4 of this mini-book, calculations can be performed as
part of a query or be made part of a table. For example, you can total the
numbers in two fields in the same record or perform mathematical
calculations on values in fields.
Deciding on a primary key field
for each database table
Each database table must have a primary key field. This field, also known as
the primary key, is the field in the database table where unique, one-of-a-kind
data is stored. Data entered in this field — an employee ID number, a part
number, a bid number — must be different in each record. If you try to enter
the same data in the primary key field of two different records, a dialog
box warns you not to do that. Primary key fields prevent you from entering
duplicate records. They also make queries more efficient. In a query, you
tell Access what to look for in database tables, Access searches through
the tables, and the program assembles information that meets the criteria.
Primary key fields help Access recognize records and not collect the same
information more than once in a query.