Полезная информация

next up previous contents index
Next: Summary Up: Joining Tables Previous: Non-Equijoins

Ordering Multiple Parts

Our customer, employee, part, and salesorder example has a serious limitation. It allows only one part per order. In the real world, this would never be acceptable. Having covered many complex join topics in this chapter, a more complete database layout can be created to allow multiple parts per order.

Figure [*] shows a new version of the salesorder table. Notice that the part_id column has been removed. The customer, employee, and part tables remain unchanged.

  
Figure: New salesorder table for multiple parts per order
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...MERIC(8,2)
\par test->~)\textbackslash{}g
\par CREATE\end{list}\par
\end{figure}

Figure [*] shows a new table, orderpart. This table is needed because the original salesorder table could hold only one part number per order. Instead of putting the part_id in the salesorder table, the orderpart table will hold one row for each part number ordered. If five part numbers are in order number 15398, there will be five rows in the orderpart table with order_id equal to 15398.

We have also added a quantity column. If someone orders seven of the same part number, we put only one row in the orderpart table, but set the quantity field equal to 7. We have used DEFAULT to set the quantity to one if no quantity is supplied or is NULL.

Notice there is no price field in the orderpart table. This is because the price is stored in the part table. Anytime the price is needed, a join is performed to get the price. This allows a part's price to be changed in one place, and all references to it automatically updated.

  
Figure: Orderpart table
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...~DEFAULT~1
\par test->~)\textbackslash{}g
\par CREATE\end{list}\par
\end{figure}

This new table layout illustrates the master / detail use of tables. The salesorder table is the master table because it holds information common to each order, such as customer and employee id's, and order date. The orderpart table is the detail table because it contains the specific parts making up the order. Master/detail tables are a common use of multiple tables.

Figure [*] shows a variety of queries using the new orderpart table.

  
Figure: Queries involving orderpart table
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...\par test->~~~~~~~~orderpart.part\_id~=~part.part\_id\end{list}\par
\end{figure}

The queries are of increasing complexity. The first query already contains the order number of interest, so there is no reason to use the salesorder table. It goes directly to the orderpart table to find the parts making up the order, and joins to the part table for part descriptions. The second query does not have the order number. It only has the the customer id and order date. It must use the salesorder table to find the order number, and then join to the orderpart and part tables to get order quantities and part information. The third query does not have the customer id, but instead must join to the customer table to get the customer_id for use with the other tables. Notice each query displays more columns to the user. The final query computes the total cost of the order. It uses an aggregate to SUM cost times (*) quantity for each part in the order.


next up previous contents index
Next: Summary Up: Joining Tables Previous: Non-Equijoins
Bruce Momjian
1999-11-21