Полезная информация Получение градостроительного плана для ижс.

next up previous contents index
Next: Creating Joined Tables Up: Joining Tables Previous: Table and Column References

Joined Tables

In our friends example, splitting data into multiple tables makes little sense. However, in examples where you must record information about a variety of things, multiple tables have benefits. Consider a company that sells parts to customers through the mail. The database has to record information about many things: customers, employees, sales orders, and parts. It is obvious a single table can not hold the different types of information in an organized manner. Therefore, we create four tables: customer, employee, salesorder, and part. However, putting information in different tables causes a problem. 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 answer is to assign unique numbers to every customer, employee, and part. When we want to record the customer in the salesorder table, we put the customer's number in the salesorder table. When we want to record which employee recorded 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, and refer to those specific entries as many times as needed by using a unique number. This is illustrated in figure [*].

  
Figure: Joining tables

\resizebox*{0.75\columnwidth}{!}{\includegraphics{joins.eps}}


People may question whether it is necessary to use separate tables. While not necessary, it is often a good idea. Without having a separate customer table, every piece of information about a customer would have to 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, like a change in telephone number, would have to be performed in all places that information is stored. With a customer table, the information is stored in one place, and each salesorder points to the customer table. This is more efficient, and allows easier administration and data maintenance. The advantages of using multiple tables are:

The only time duplicate data should not be moved to a separate table is when:

The customer, employee, part, and salesorder example clearly benefits from multiple tables.8.1


next up previous contents index
Next: Creating Joined Tables Up: Joining Tables Previous: Table and Column References
Bruce Momjian
1999-11-21