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