Ordering Multiple Parts

Our mail-order example has a serious limitation: It allows only one part_id per salesorder. In the real world, this restriction would not be acceptable. Now that we have covered many complex join topics in this chapter, we are ready to create a more complete database layout that allows for 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.  

        CREATE TABLE salesorder (
                             order_id      INTEGER,
                             customer_id   INTEGER,  -- joins to customer.customer_id
                             employee_id   INTEGER,  -- joins to employee.employee_id
                             order_date    DATE,
                             ship_date     DATE, 
                             payment       NUMERIC(8,2)
        );  

Figure  shows a new table, orderpart.  

        CREATE TABLE orderpart(
                               order_id INTEGER,
                               part_id  INTEGER,
                               quantity INTEGER DEFAULT 1
        );  

This table is needed because the original salesorder table could hold only one part number per order. Instead of having part_id in the salesorder table, the orderpart table holds one row for each part number ordered. If five part numbers are in order number 15398, then five rows will appear in the orderpart table with order_id equal to 15398.

We also add a quantity column. If a customer 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 use DEFAULT to set the quantity to 1 if no quantity is specified. 

Notice that the orderpart table does not include a price field. Instead, the price is stored in the part table. Whenever the price is needed, a join is performed to get the price. This choice allows us to change a part's price in one place, and all references to it will be updated automatically.^11.4

The 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 identifiers 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.  

        -- first query
        SELECT part.name                                  
        FROM   orderpart, part
        WHERE  orderpart.part_id = part.part_id AND
               orderpart.order_id = 15398; 
         
        -- second query
        SELECT part.name, orderpart.quantity
        FROM salesorder, orderpart, part
        WHERE salesorder.customer_id = 648 AND
              salesorder.order_date = '7/19/1994' AND
              salesorder.order_id = orderpart.order_id AND
              orderpart.part_id = part.part_id; 
         
        -- third query
        SELECT part.name, part.cost, orderpart.quantity
        FROM   customer, salesorder, orderpart, part
        WHERE  customer.name = 'Fleer Gearworks, Inc.' AND
               salesorder.order_date = '7/19/1994' AND
               salesorder.customer_id = customer.customer_id AND
               salesorder.order_id = orderpart.order_id AND
               orderpart.part_id = part.part_id; 
         
        -- fourth query
        SELECT SUM(part.cost * orderpart.quantity)
        FROM   customer, salesorder, orderpart, part
        WHERE  customer.name = 'Fleer Gearworks, Inc.' AND
               salesorder.order_date = '7/19/1994' AND
               salesorder.customer_id = customer.customer_id AND
               salesorder.order_id = orderpart.order_id AND
               orderpart.part_id = part.part_id;  

The queries demonstrate 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, joining to the part table to obtain part descriptions. The second query does not have the order number, only the customer_id and order_date. It must use the salesorder table to find the order number, 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 that each query displays an increasing number of 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.