Chapter 5 -- Designing Databases

In Visual FoxPro, you use databases to organize and relate tables and views. Databases provide the architecture for storing your data and have additional benefits as well. When you use a database, you can create table-level extensions such as field- and record-level rules, default field values, and triggers. You can also create stored procedures and persistent table relationships. You can use your database to access connections to remote data sources and to create views of local and remote tables.

This chapter provides guidelines for planning the tables that go into a Visual FoxPro database. It walks you through the database design of the Tasmanian Traders sample database and provides you with additional sample database designs. The Tasmanian Traders sample database, Tastrade.dbc, is located in the Visual Studio \Samples\Vfp98\Tastrade\Data directory.

For information on creating Visual FoxPro databases after you design them, see Chapter 6, Creating Databases. For information on creating Visual FoxPro tables, see Chapter 7, Working with Tables.

This chapter discusses:

• Using a Database Design Process
• Analyzing Data Requirements
• Grouping Requirements into Tables
• Determining the Fields You Need
• Identifying Relationships
• Refining the Design
• Sample Database Diagrams

Using a Database Design Process

If you use an established database design process, you can quickly and effectively create a well-designed database that provides you with convenient access to the information you want. With a solid design, you ll spend less time constructing the database and you ll end up with faster, more accurate results.

Note   The terms database and table are not synonymous in Visual FoxPro. The term database (.dbc file) refers to a relational database that is a container of information about one or more tables (.dbf files) or views.

The key to effective database design lies in understanding exactly what information you want to store and the way a relational database management system, such as Visual FoxPro, stores data. To efficiently and accurately provide you with information, Visual FoxPro needs to have the facts about different subjects organized into separate tables. For example, you might have one table that stores facts only about employees and another that stores facts only about sales.

When you organize your data appropriately, you design flexibility into your database and gain the capability to combine and present facts in many different ways. For example, you can print reports that combine facts about employees and facts about sales.

Separating facts into tables adds flexibility to your database.

When you design a database, you first break down the information you want to keep as separate subjects, and then you tell Visual FoxPro how the subjects are related to each other so that Visual FoxPro can bring the right information together when you need it. By maintaining information in separate tables, you make it easier to organize and maintain your data, as well as to build a high-performance application.

Here are the steps in the database design process. Each step is discussed in greater detail in the remaining sections of this chapter.

1. Determine the purpose of your database   Knowing the purpose will help you decide which facts you want Visual FoxPro to store.
   
   
2. Determine the tables you need   When you have a clear purpose for your database, you can divide your information into separate subjects, such as Employees or Orders. Each subject will be a table in your database.
   
   
3. Determine the fields you need   Decide what information you want to keep in each table. Each category of information in a table is called a field and is displayed as a column when you browse the table. For example, one field in an Employee table could be Last_name; another could be Hire_date.
   
   
4. Determine the relationships   Look at each table and decide how the data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.
   
   
5. Refine your design   Analyze your design for errors. Create the tables and add a few records of sample data. See if you can get the results you want from your tables. Make adjustments to the design as needed.

Don t worry if you make mistakes or leave things out of your initial design. Think of it as a rough draft that you can refine later. Experiment with sample data and prototypes of your forms and reports. With Visual FoxPro, it s easy to change the design of your database as you re creating it. However, it becomes much more difficult to make changes to tables after they re filled with data and after you ve built forms and reports. For this reason, make sure that you have a solid design before you proceed too far into building your application.

Analyzing Data Requirements

Your first step in designing a Visual FoxPro database is to analyze your data requirements by determining the purpose of the database and how it is to be used. This tells you what information you want from the database. From that, you can determine what subjects you need to store facts about (the tables) and what facts you need to store about each subject (the fields in the tables).

Talk to the people who will use the database. Brainstorm about the questions you d like the database to answer. Sketch out the reports you d like it to produce. Gather the forms you currently use to record your data. You ll use all this information in the remaining steps of the design process.

Example: Tracking Sales and Inventory

Suppose that Tasmanian Traders, an import/export company that sells specialty foods from around the world, wants a database that can track information about the company s sales and inventory.

Start by writing down a list of questions the database should be able to answer. How many sales of our featured product did we make last month? Where do our best customers live? Who s the supplier for our best-selling product?

Next, gather all the forms and reports that contain information the database should be able to produce. The company currently uses a printed report to keep track of products being ordered and an order form to take new orders. The following illustration shows these two documents.

Forms and reports show some data requirements for your database.

Tasmanian Traders also needs to print mailing labels for customers, employees, and suppliers.

After gathering this information, you re ready for the next step.

Grouping Requirements into Tables

Determining the tables in your database can be the trickiest step in the database design process. That s because the results you want from your database the reports you want to print, the forms you want to use, and the questions you want answered don t necessarily provide clues about the structure of the tables that produce them. They tell you what you want to know but not how to categorize the information into tables.

See the preceding order form as an example. It includes facts about the customer the customer s address and phone number along with facts about the order. This form provides you with a number of facts that you know you want to store in your database. Although the facts are all on the same form, you can easily prevent common data integrity problems by storing them in separate tables.

Storing information once reduces chance of error   For example, if you only use one table to store the information for an order form, suppose that one customer places three different orders. You could add the customer s address and phone number to your database three times, once for each order. But this multiplies the chance of data entry errors.

The Customer table stores address information once.

Also, if the customer moves, you d have to either accept contradictory information or find and change each of that customer s sales records in the table. It s much better to create a Customer table that stores the customer s address in your database once. Then, if you need to change the data, you change it only once.

Preventing deletion of valuable information   Suppose a new customer places an order and then cancels. When you delete the order from the table containing information on both customers and their orders, you would delete the customer s name and address as well. But you want to keep this new customer in your database so you can send the customer your next catalog. Again, it s better to put the information about the customer in a separate Customer table. That way you can delete the order without deleting customer information.

Look at the information you want to get out of your database and divide it into fundamental subjects you want to track, such as customers, employees, products you sell, services you provide, and so on. Each of these subjects is a candidate for a separate table.

Tip   One strategy for dividing information into tables is to look at individual facts and determine what each fact is actually about. For example, on the Tasmanian Traders order form, the customer address isn t about the sale; it s about the customer. This suggests that you need a separate table for customers. In the Products On Order report, the supplier s phone number isn t about the product in stock; it s about the supplier. This suggests that you need a separate table for suppliers.

Example: Designing Tables in the Tasmanian Traders Database

The Tasmanian Traders Order Form and Products On Order report include information about these subjects:

• Employees
• Customers
• Suppliers
• Products
• Orders

From this list, you can come up with a rough draft of the tables in the database and some of the fields for each table.

Rough draft of tables and fields required for Tasmanian Traders database

Although the finished Tasmanian Traders database contains other tables, this list is a good start. Later in this chapter, you ll see how to add other tables to refine your design.

Determining the Fields You Need

To determine the fields in a table, decide what you need to know about the people, things, or events recorded in the table. You can think of fields as attributes of the table. Each record (or row) in the table contains the same set of fields or attributes. For example, an address field in a customer table contains customers addresses. Each record in the table contains data about one customer, and the address field contains the address for that customer.

Identifying Fields

Here are a few tips for determining your fields:

Relate each field directly to the subject of the table   A field that describes the subject of a different table belongs in that other table. Later, when you define relationships between your tables, you ll see how you can combine the data from fields in multiple tables. For now, make sure that each field in a table directly describes the subject of the table. If you find yourself repeating the same information in several tables, it s a clue that you have unnecessary fields in some of the tables.

Don t include derived or calculated data   In most cases, you don t want to store the result of calculations in tables. Instead, you can have Visual FoxPro perform the calculations when you want to see the result. For example, the order form shown earlier in this chapter displays the extended price for each line of the order in the Tasmanian Traders database. However, there s no Extended Price subtotal field in any Tasmanian Traders table. Instead, the Order_Line_Items table includes a quantity field that stores the units on order for each individual product, as well as the unit price for each item ordered. Using that data, Visual FoxPro calculates the subtotal each time you print an order form. The subtotal itself doesn t need to be stored in a table.

Include all the information you need   It s easy to overlook important information. Return to the information you gathered in the first step of the design process. Look at your paper forms and reports to make sure all the information you have required in the past is included in your Visual FoxPro tables or can be derived from them. Think of the questions you will ask Visual FoxPro. Can Visual FoxPro find all the answers using the information in your tables? Have you identified fields that will store unique data, such as the customer ID? Which tables include information that you ll combine into one report or form?  For more information on identifying key fields and relating tables, see the sections Using Primary Key Fields and Identifying Relationships later in this chapter.

Store information in its smallest logical parts   You might be tempted to have a single field for full names, or for product names, along with product descriptions. If you combine more than one kind of information in a field, it s difficult to retrieve individual facts later. Try to break down information into logical parts; for example, create separate fields for first and last name, or for product name, category, and description.

Example: Adding Fields to the Products Table

Tasmanian Traders sells imported specialty foods from around the world. The employees use a Products On Order report to keep track of products being ordered.

Report for tracking the inventory of  products

The report indicates that the Products table, which contains facts about products sold, needs to include fields for the product name, units in stock, and units on order, among others. But what about fields for the supplier name and phone number? To produce the report, Visual FoxPro needs to know which supplier goes with each product.

Draft of the Supplier table containing fields for supplier name and phone number

You can solve this without storing redundant data in your tables by creating a Supplier table with separate fields for the supplier s name and phone number. In the next step, you ll add a field to the Products table that identifies the supplier information you need.

Using Primary Key Fields

The power in a relational database management system such as Visual FoxPro comes from its ability to quickly find and bring together information stored in separate tables. In order for Visual FoxPro to work most efficiently, each table in your database should include a field or set of fields that uniquely identifies each individual record stored in the table. This is often a unique identification number, such as an employee ID number or a serial number. In database terminology, this information is called the primary key of the table. Visual FoxPro uses primary key fields to quickly associate data from multiple tables and bring the data together for you.

If you already have a unique identifier for a table, such as a set of product numbers you ve developed to identify the items in your stock, you can use that identifier as the table s primary key. But make sure the values in this field will always be different for each record Visual FoxPro doesn t allow duplicate values in a primary key field. For example, don t use someone s name as a primary key, because names aren t unique. You could easily have two people with the same name in the same table.

When choosing primary key fields, keep these points in mind:

• Visual FoxPro doesn t allow duplicate or null values in a primary key field. For this reason, you shouldn t choose a primary key that could contain such values.
• You can use the value in the primary key field to look up records, so it shouldn t be too long to remember or type. You might want it to have a certain number of letters or digits, or be within a certain range of values.
• The size of the primary key affects the speed of operations in your database. When you create primary key fields, use the smallest size that will accommodate the values you need to store in the field.

Example: Setting the Primary Key for the Products Table

The primary key of the Tasmanian Traders Products table contains product ID numbers. Because each product number identifies a different product, you don t want two products with the same number.

The Primary key for the Products table is the Product_id field.

In some cases, you might want to use two or more fields that together provide the primary key of a table. For example, the Order_Line_Items table in the Tasmanian Traders database uses two fields as its primary key: Order_id and Product_id. In the next step, you ll see why.

Identifying Relationships

Now that you ve divided your information into tables, you need a way to tell Visual FoxPro how to bring it back together again in meaningful ways. For example, the following form includes information from several tables.

The Order Entry form uses information from several tables.

Visual FoxPro is a relational database management system. That means you store related data in separate tables. Then you define relationships between the tables and Visual FoxPro uses the relationships to find associated information stored in your database.

For example, suppose that you want to phone an employee with questions about a sale the employee made. Employee phone numbers are recorded in the Employee table; sales are recorded in the Orders table. When you tell Visual FoxPro which sale you re interested in, Visual FoxPro can look up the phone number based on the relationship between the two tables. It works because Employee_id, the primary key for the Employee table, is also a field in the Orders table. In database terminology, the Employee_id field in the Orders table is called a foreign key, because it refers to a primary key from a different, or foreign, table.

Employee_id field as primary key for Employee table and foreign key for Orders table

So, to set up a relationship between two tables Table A and Table B you add one table s primary key to the other table, so that it appears in both tables. But how do you decide which table s primary key to use? To set up the relationship correctly, you must first determine the nature of the relationship. There are three types of relationships between tables:

• One-to-many relationships
• Many-to-many relationships
• One-to-one relationships

The rest of this section presents an example of each type of relationship and explains how to design your tables so that Visual FoxPro can associate the data correctly. The purpose of each example is to explain how you determine the relationships between your tables and how you decide which fields belong in the tables to support those relationships it doesn t describe how to use the Visual FoxPro interface to relate tables.

Example: Creating a One-to-Many Relationship

A one-to-many relationship is the most common type of relationship in a relational database. In a one-to-many relationship, a record in Table A can have more than one matching record in Table B, but a record in Table B has, at most, one matching record in Table A.

For example, the Category and Products tables in the Tasmanian Traders database have a one-to-many relationship.

The Category and Products tables represent a one-to-many relationship.

To set up the relationship, you add the field or fields that make up the primary key on the one side of the relationship to the table on the many side of the relationship. You use a primary or candidate index key for the one side of the relationship, and a regular index key for the many side. In this case, you would add the Category_id field from the Category table to the Products table, because one category includes many products. Visual FoxPro uses the category ID number to locate the correct category for each product.

For information about creating index keys, see Chapter 7, Working with Tables.

Example: Creating a Many-to-Many Relationship

In a many-to-many relationship, a record in Table A can have more than one matching record in Table B, and a record in Table B can have more than one matching record in Table A. This type of relationship requires changes in your database design before you can correctly specify the relationship to Visual FoxPro.

To detect many-to-many relationships between your tables, it s important that you look at both directions of the relationship. For example, consider the relationship between orders and products in the Tasmanian Traders business. One order can include more than one product. So for each record in the Orders table, there can be many records in the Products table. But that s not the whole story. Each product can appear on many orders. So for each record in the Products table, there can be many records in the Orders table.

The Orders and Products tables represent a many-to-many relationship.

The subjects of the two tables orders and products have a many-to-many relationship. This presents a challenge in database design. To understand the problem, imagine what would happen if you tried to set up the relationship between the two tables by adding the Product_id field to the Orders table. To have more than one product per order, you need more than one record in the Orders table per order. You d be repeating order information over and over for each record that relates to a single order an inefficient design that could lead to inaccurate data. You run into the same problem if you put the Order_id field in the Products table you d have more than one record in the Products table for each product. How do you solve this problem?

The answer is to create a third table that breaks down the many-to-many relationship into two one-to-many relationships. This third table is called a junction table, because it acts as the junction between two tables. You put the primary key from each of the two tables into the junction table.

The Order_Line_Items table creates a one-to-many link between Orders and Products.

A junction table might hold only the two primary keys from the tables it links together or, as in the Order_Line_Items table, the junction table might hold additional information.

Each record in the Order_Line_Items table represents one line item on an order. The Order_Line_Items table s primary key consists of two fields the foreign keys from the Orders and Products tables. The Order_id field alone doesn t work as the primary key for this table, because one order can have many line items. The order ID is repeated for each line item on an order, so the field doesn t contain unique values. The Product_id field alone doesn t work either, because one product can appear on many different orders. But together the two fields in the junction table always produce a unique value for each record. The junction table does not require its own primary key.

In the Tasmanian Traders database, the Orders table and the Products table aren t related to each other directly. Instead, they are related indirectly through the Order_Line_Items table. The many-to-many relationship between orders and products is represented in the database using two one-to-many relationships:

• The Orders and Order_Line_Items tables have a one-to-many relationship. Each order can have more than one line item, but each line item is connected to only one order.
• The Products and Order_Line_Items tables have a one-to-many relationship. Each product can have many line items associated with it, but each line item refers to only one product.

Example: Creating a One-to-One Relationship

In a one-to-one relationship, a record in Table A can have no more than one matching record in Table B, and a record in Table B can have no more than one matching record in Table A. This type of relationship is unusual and might call for some changes in your database design.

One-to-one relationships between tables are unusual because in many cases, the information in the two tables can simply be combined into one table. For example, suppose you created a table, called Ping-Pong Players, to track information about a Tasmanian Traders Ping-Pong fundraising event. Because the ping-pong players are all employees of Tasmanian Traders, this table has a one-to-one relationship with the Employee table in the Tasmanian Traders database.

The Employee and Ping_Pong_Players represent a one-to-one relationship.

You could add all the fields from the Ping-Pong Players table to the Employee table. But the Ping-Pong Players table tracks a one-time event, and you won t need the information after the event is over. Additionally, not all employees play Ping-Pong, so if these fields were in the Employee table, they would be empty for many records. For these reasons, it makes sense to create a separate table.

When you detect the need for a one-to-one relationship in your database, consider whether you can put the information together in one table. For example, in the Employee table, one employee can have one manager, who is also an employee. You can add a field for the manager s id number. To pull the information together later, you can use a self join in your query or view. You don't need a separate table to resolve the one-to-one relationship. If you don t want to do that for some reason, here s how to set up the one-to-one relationship between two tables:

• If the two tables have the same subject, you can probably set up the relationship by using the same primary key field in both tables.
• If the two tables have different subjects with different primary keys, choose one of the tables (either one) and put its primary key field in the other table as a foreign key.

Refining the Design

When you have the tables, fields, and relationships you need, it s time to study the design and detect any flaws that might remain.

You might encounter several pitfalls while you are designing your database. These common problems can cause your data to be harder to use and maintain:

• Do you have one table with a large number of fields that don t all relate to the same subject? For example, one table might contain fields pertaining to your customers as well as fields that contain sales information. Try to make sure each table contains data about only one subject.
• Do you have fields that are intentionally left blank in many records because they aren t applicable to those records? This usually means that the fields belong in another table.
• Do you have a large number of tables, many of which contain the same fields? For example, you have separate tables for January sales and February sales, or for local customers and remote customers, in which you store the same type of information. Try consolidating all the information pertaining to a single subject in one table. You might also need to add an extra field, for example, to identify the sales date.

Create your tables, specify relationships between the tables, and enter a few records of data in each table. See if you can use the database to get the answers you want. Create rough drafts of your forms and reports and see if they show the data you expect. Look for unnecessary duplications of data and eliminate them.

As you try out your initial database, you will probably discover room for improvement. Here are a few things to check for:

• Did you forget any fields? Is there information that you need that isn t included? If so, does it belong in the existing tables? If it s information about something else, you might need to create another table.
• Did you choose a good primary key for each table? If you use it to search for specific records, is it easy to remember and type? Make sure that you won t need to enter a value in a primary key field that duplicates another value in the field.
• Are you repeatedly entering duplicate information in one of your tables? If so, you probably need to divide the table into two tables with a one-to-many relationship.
• Do you have tables with many fields, a limited number of records, and many empty fields in individual records? If so, think about redesigning the table so it has fewer fields and more records.

As you identify the changes you want to make, you can alter your tables and fields to reflect the improved design. For information about modifying tables, see Chapter 7, Working with Tables.

Example: Refining the Products Table

Each product in the Tasmanian Traders stock falls under a general category, such as Beverages, Condiments, or Seafood. The Products table could include a field that shows the category of each product.

Products table with a Category_name field

Suppose that in examining and refining the database,Tasmanian Traders decides to store a description of the category along with its name. If you add a Category Description field to the Products table, you have to repeat each category description for each product that falls under the category not a good solution.

A better solution is to make Category a new subject for the database to track, with its own table and its own primary key. Then you can add the primary key from the Category table to the Products table as a foreign key.

The Category table provides a place to store category information efficiently.

The Category and Products tables have a one-to-many relationship: one category can have more than one product in it, but any individual product can belong to only one category.

Sample Database Diagrams

The database diagrams in this section might give you ideas for designing your own database. These databases aren t included with Visual FoxPro; they re included here as examples of the types of databases and tables you can create.

Appointments Database

This database structure stores appointments for a professional office, and could easily be modified for use in an office of doctors, dentists, lawyers, or accountants. The Appointments table has a multiple-field primary key to uniquely identify each appointment. This primary key, the client_sta index, is created by indexing on an expression that combines the client_id and date_start time fields.

Example of an appointments database

Personnel Database

This database structure stores human resources information. The Job History table stores information on each hire or promotion, so it can contain many records for each employee.

Example of a personnel database

Library Database

This database stores information about library books and loans to patrons. Notice the many-to-many relationships between the Books and Authors tables and between the Books and Subjects tables.

Example of a library database