Grouping logically-related fields into distinct tables, determining key fields, and then relating distinct tables using common key fields is called rationalizing a database. There are two major reasons for designing a database this way:
- To avoid wasting storage space for redundant data
- To eliminate the complication of updating duplicate data copies
For example, in the Customers/Orders database, we want to be able to identify the customer name, address, and phone number for each order, but we want to avoid repeating that information for each order. To do so would take up storage space needlessly andmake the job of updating multiple customer addresses difficult and time-consuming.
To avoid redundancy:
- Place all the fields related to customers (name, address, etc.) into a Customer table and create a Primary key field which uniquely identifies each customer: Customer ID.
- Put all the fields related to orders (date, salesperson, total, etc.) into the Orders table.
- Include the Primary key field (Customer ID) from the Customer table in the table for Orders.
The One-to-Many relationship between Customer and Orders is defined by the common field Customer ID. In the table for Customers (the “one” table) Customer ID is a primary key, while in the Orders table (the “many” table) it is a foreign key.