Refinement of 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 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.

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.

See Also

Identification of Relationships | Sample Database Diagrams | Determination of the Fields You Need | Organization of Requirements into Tables | Analysis of Data Requirements | Designing Databases | Creating Databases | Working with Tables