Microsoft Office Tutorials and References
In Depth Information
Designing a Database
Social security numbers make good primary key fields because no two
people have the same social security number. Invoice numbers and serial
numbers also make excellent primary key fields. Returning to the sample
baseball league database (refer to Figure 1-7), which fields in the little league
database tables are primary key fields? In the Teams table, Team Name can
be the primary key field because no two teams have the same name. Division
Number can also be a primary key field because divisions in the league are
numbered and no two divisions have the same number.
The Players and Coaches database tables, however, present a problem when
it comes to choosing a primary key field. Two players might have the same
last name, which rules out Last Name as a primary key field. A brother and
sister might have the same telephone number, which rules out a Telephone
No. field. Because no field holds values that are certain to be different
from record to record, I introduce fields called Player Number and Coach
Number. For the purpose of this database, players and coaches are assigned
numbers. (Chapter 2 in this mini-book explains how Access can assign
sequential numbers for you in a database table.)
Mapping the relationships between tables
If your database includes more than one table, you have to map how the
tables relate to one another. Usually, relationships are formed between the
primary key field in one table and the corresponding field in another, called
the foreign key. Figure 1-8 shows the relationships between the tables in the
little league database. Because these tables are linked by common fields,
I can gather information from more than one table in a query or report.
Chapter 2 in this mini-book takes up the subject of linking tables in more
detail. For now, when you design your database, consider how to connect
the various tables with common fields.
Figure 1-8:
Table
relationships.
Search JabSto ::




Custom Search