JavaScript Editor js editor     Web development 



Main Page

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:

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:

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 Working with Tables.

Example

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.

FoxProProductCategory screenshot

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.

FoxProCategoryProductKeys screenshot

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.

See Also



JavaScript Editor js editor     Web development