Organization of 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 order form in Analysis of Data Requirements 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:

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. In other sections, you'll see how to add other tables to refine your design.

See Also

Analysis of Data Requirements | Determination of the Fields You Need | Designing Databases | Creating Databases | Working with Tables