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.
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.
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).
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.
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.
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.
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.
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 names 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.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. 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 relationshipsRefining 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: