Microsoft Office Tutorials and References
In Depth Information
Session 2.1
Session 2.1
Guidelines for Designing Databases
A database management system can be a useful tool, but only if you first carefully design
the database so that it meets the needs of its users. In database design, you determine the
fields, tables, and relationships needed to satisfy the data and processing requirements.
When you design a database, you should follow these guidelines:
For hands-on practice of
key tasks in this session,
go to the SAM 2003
Training Companion CD
included with this text.
Identify all the fields needed to produce the required information. For example, Elsa
needs information about employers, NAICS codes, and positions. Figure 2-1 shows the
fields that satisfy these information requirements.
Figure 2-1
Elsa’s data requirements
EmployerID
EmployerID
ContactFirstName
ContactFirstName
PositionID
PositionID
ContactLastName
ContactLastName
PositionTitle
PositionTitle
Position
Position
EmployerName
EmployerName
Wage
Wage
Address
Address
HoursPerWeek
HoursPerWeek
City
City
NAICSCode
NAICSCode
StateProv
StateProv
NAICSDesc
NAICSDesc
PostalCode
PostalCode
StartDate
StartDate
Country
Country
EndDate
EndDate
Phone
Phone
ReferredBy
ReferredBy
Openings
Openings
Website
Website
Group related fields into tables. For example, Elsa grouped the fields relating to
employers into the Employer table and the fields related to NAICS codes into the NAICS
table. The other fields are grouped logically into the Position table, which you will cre-
ate, as shown in Figure 2-2.
Figure 2-2
Elsa’s fields grouped into tables
Employer table
EmployerID
EmployerName
Address
City
StateProv
PostalCode
Country
ContactFirstName
ContactLastName
Position
Phone
Website
NAICS table
NAICSCode
NAICSDesc
Position table
PositionID
PositionTitle
Wage
HoursPerWeek
Openings
ReferredBy
StartDate
EndDate
Determine each table’s primary key. Recall that a primary key uniquely identifies each
record in a table. Although a primary key is not mandatory in Access, it’s usually a good idea
to include one in each table. Without a primary key, selecting the exact record that you want
can be a problem. For some tables, one of the fields, such as a Social Security or credit card
 
Search JabSto ::




Custom Search