Joined Tables

In our friend example, splitting data into multiple tables makes little sense. However, in cases where we must record information about a variety of things, multiple tables have benefits. Consider a company that sells parts to customers through the mail. Its database has to record information about many things: customers, employees, sales orders, and parts. It is obvious that a single table cannot hold these different types of information in an organized manner. Therefore, we create four tables: customer, employee, salesorder, and part. Unfortunately, putting information in different tables also causes problems. How do we record which sales orders belong to which customers? How do we record the parts for the sales orders? How do we record which employee received the sales order? The solution is to assign unique numbers to every customer, employee, and part. When we want to record the customer in the salesorder table, for example, we put the customer's number in the salesorder table. When we want to record which employee took the order, we put the employee's number in the salesorder table. When we want to record which part has been ordered, we put the part number in the salesorder table.

Breaking up the information into separate tables allows us to keep detailed information about customers, employees, and parts. It also allows us to refer to those specific entries as many times as needed by using a unique number. Figure  illustrates the joining of the separate tables we will use.

People might question the choice of using separate tables. While not necessary, it is often useful. Without a separate customer table, every piece of information about a customer would have to be stored in the salesorder table every time a salesorder row was added. The customer's name, telephone number, address, and other information would have to be repeated. Any change in customer information, such as a change in telephone number, would have to be performed in all places in which that information is stored. With a customer table, the information is stored in one place, and each salesorder points to the customer table. This approach is more efficient, and it allows for easier administration and data maintenance. The advantages of using multiple tables include the following:

• Easier data modification
• Easier data lookup
• Data stored in only one place
• Less storage space required

The only time duplicate data should not be moved to a separate table is when all of the following conditions are present:

• The time required to perform a join is prohibitive.
• Data lookup is unnecessary.
• Duplicate data require little storage space.
• Data are very unlikely to change.

The customer, employee, part, and salesorder example clearly benefits from multiple tables. The process of distributing data across multiple tables to prevent redundancy is called data normalization.