Figure 14-2 shows some sample records from the orders table.
Figure 14-2. Sample data in the orders table
How It Works: The order_detail Table
Let??™s take a look at Figure 14-3 to see some examples of records in the order_detail table.
Figure 14-3. Sample data in the order_detail table
Each record in order_detail represents an ordered product that belongs to the order specified by order_id.When
forming the primary key of this table, we face the same limitation that we had when creating the shopping_cart
table. Normally, the primary key could be formed from (order_id, product_id, attributes), but since this not
possible, we created an additional field named item_id to act as the primary key.
We also store the product name, attributes, quantity, and price (unit_cost). You might be wondering why we
record this data, since we already have the product_id field, which can lead to that data. It??™s important to understand
that the data we record for an order is stored for historical purposes. Product IDs, names, and prices may
change, but to prevent these changes from affecting the details of an order placed in the past, we must be sure we
record them in a table unaffected by product changes.We store product_id, because it??™s the only programmatic
way to link back to the original product information (if the product still exists).
CHAPTER 14 ?– ACCEPTING CUSTOMER ORDERS 435
Implementing the Data Tier
At this stage, you need to add two additional data tier stored procedures in the tshirtshop
database.
Pages:
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552