Determine the Goals of the Database
A poorly designed database is of less than no value. The more time spent on task and data
analysis, the better the results will be. To begin the database design, start at the end point. Find
out what the end user expects of the database, then go about structuring the design to provide
these requirements.
To do so, answer the following questions:
¦
What do the users want to get from the database.
¦
What kinds of reports are needed (how do users want the information arranged and
summarized).
Then follow these directions:
¦
If adequate data collection forms already exist, use them as patterns for the Access forms.
¦
Look at other databases that address similar information management situations and see
whether they can provide any guidance with the design of your own.
¦
Once the tasks have been defined, develop a list of the required data items.
The main purpose of the Home Tech Repair database is to maintain up-to-date information
about current work orders. To do this, it must relate the individual work orders to specific
customers or employees. It also must include forms for data entry and viewing of all table data.
In addition to the work order tracking, the owner would like to be able to conduct financial
analyses; for example, to determine how much revenue has been generated by each employee or
to review the total sales on a monthly basis. These analyses can include summary reports with
charts and graphs depicting trends and proportional distributions of types of jobs over a period
of time. Such studies are helpful when planning for future work.
Distribute the Data Among the Tables
Distributing data is the cornerstone of relational databases. The efficiency and effectiveness of
such a database relies on the proper distribution of data among the tables that make up the database.
This is not as easy as it sounds, but here are some guidelines to follow:
¦
The information in a table should be limited to a single subject. This allows you to
maintain data about each subject independently of the others.
¦
Tables should not contain duplicate information among its records. With only one
copy of each data item, you need to update it in only one place.
In the Home Tech Repair case, employee and customer information is repeated on several
manually prepared work order sheets. To reduce the redundancy, pull out both sets of information
and put them in separate tables. Keeping payments in a separate table will add flexibility, especially
if the work is paid for in installments, such as a deposit at the start of the contract and the remainder
while the work is being completed.
28
How to Do Everything with Microsoft Office Access 2003
HowTo-Tght (8) / How to Do Everything with Microsoft Office Access 2003 / Andersen / 222938-1 / Chapter 2
P:\010Comp\HowTo8\938-1\ch02.vp
Thursday, August 07, 2003 6:32:00 PM
Color profile: Generic CMYK printer profile
Composite Default screen
|
|