Databases Reference
In-Depth Information
The ideal strategy combines the best of both approaches. Assuming the design problem is sufficiently com-
plicated to warrant the benefits of the top-down approach, you could begin the design process for Premiere
Products using a top-down approach by completing the following steps:
1. After gathering data on all user views, review them without attempting to create any tables. In
other words, try to get a general feel for the task at hand.
2. From this information, determine the basic entities of interest to the organization (sales reps, cus-
tomers, orders, and parts). Do not be overly concerned that you might miss an entity. If you do
miss one, it will show up in later steps of the design method.
3. For each entity, start a table. For example, if the entities are sales reps, customers, orders, and
parts, you will have the following:
Rep (
Customer (
Orders (
Part (
4. Determine and list a primary key for each table. In this example, you might have the following:
Rep (RepNum,
Customer (
CustomerNum
,
Orders (OrderNum,
Part (
PartNum
,
5. For each one-to-many relationship you can identify among these entities, optionally create and docu-
ment an appropriate foreign key. For example, if there is a one-to-many relationship between the Rep
and Customer tables, add the foreign key RepNum to the Customer table. If you omit this step or fail
to list any foreign keys, you'll usually find them when you examine the individual users views later.
After completing the steps for a top-down approach, you can then apply the bottom-up method for examin-
ing individual user views. As you design each user view, keep in mind the tables you have created in the initial top-
down approach and their keys. When you need to determine the primary key for a table, look for a primary key
in your cumulative design. When it is time to determine a foreign key, check the entity's primary key to see if a
match exists in the cumulative design. In either case, if the primary key already exists, use the existing name as
a foreign key to ensure that you can merge the tables properly. At the end of the design process, you can con-
sider removing any tables that do not contain columns and that have no foreign keys matching them.
Adding these steps to the process brings the benefits of the top-down approach to the approach you have
been using. As you proceed through the design process for the individual user views, you will have a gen-
eral idea of the overall picture.
197
When designing a database, you might find it helpful to design a survey form to obtain the required information
from users. You can ask users to complete the form, or you may want to complete the form yourself dur-
ing an interview with the user. Before beginning the interview, you can identify all existing data by viewing vari-
ous reports, documents, and so on. In any case, it is imperative that the completed survey form contain all
the information necessary for the design process.
To be truly valuable to the design process, the survey form must contain the following information:
Entity information.
For each entity (reps, customers, parts, and so on), record a name and
description and identify any synonyms for the entity. For example, at Premiere Products, your sur-
vey might reveal that what one user calls “parts” another user calls “products.” In addition,
record any general information about the entity, such as its use within the organization.
●
Attribute (column) information.
For each attribute of an entity, list its name, description, syn-
onyms, and physical characteristics (such as being 20 characters long and alphanumeric or a num-
ber with five digits), along with general information concerning its use. In addition, list any
restrictions on values and the place from which the values for the item originate. (For example,
the values might originate from time cards or from orders placed by customers or be com-
puted from values from other attributes, such as when subtracting the balance from the credit limit
to obtain available credit). Finally, list any security restrictions that apply to the attribute.
●
