Organizing 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 Analyzing 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 the 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 the deletion of valuable information
Suppose a new customer places an order and then cancels it. 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.