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 section 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 Identification of Relationships.

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:

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.

See Also

Determination of the Fields You Need | Using Primary Key Fields