To motivate this example, consider a customer, Dimitria Marzalla, who has added two bottles of the new De Morton Wines Curry Hill Cabernet Merlot 1996 to her shopping cart and now wishes to purchase the wines.
Before showing you how the purchase is finalized, let's examine the information recorded in the user shopping cart and what we know about the user.
First, we know that
cust_id=1 is the ID for this customer and that the wine being purchased has
wine_id=1029. This associated information has been previously determined in the process of collecting data for the purchase in the online winestore.
Second, we need to consider how the shopping cart is managed in the winestore. We use the orders and items tables to manage the shopping cart for each user. When a user adds the first item to her shopping cart, a new row is created in the orders table with a dummy
cust_id=-1 and the next available
order_id for this dummy customer. We use a dummy customer number because customers don't need to log in to add wine to their shopping carts, and because finalized orders are distinguished by having the
cust_id of a customer who is a member.
For this example, assume that the shopping cart has
order_id=354, and the dummy customer is
cust_id=-1. Also assume that the row in the items table that represents the wine in the shopping cart has a
wine_id=1029, a quantity
qty=2, and the price information for the wine. The price is $14.95 per bottle.
Before finalizing an order, we need to determine if there are two bottles of the wine available. A wine can be added to the shopping cart if there is any stock available, but this doesn't necessarily mean that there is more than one bottle left or that another user has not purchased the wine in the meantime. If there is sufficient wine available to finalize an order, we reduce the on-hand stock by two bottles. Checking if there are two bottles available can be done with the following query:
SELECT SUM(on_hand) FROM inventory WHERE wine_id=1029;
wine_id is unnecessary in this case because only one wine is selected.
Assuming there are more than two bottles available, we need to reduce the on-hand stock, beginning with the oldest inventory; this was one of the system requirements defined in Chapter 1. There are several ways to find the oldest inventory and the wine per-bottle price. A simple technique is to inspect the inventories:
SELECT inventory_id,cost,on_hand FROM inventory WHERE wine_id=1029 ORDER BY date_added;
The oldest (and only)
inventory_id=1, and there is an on-hand stock of 24 bottles. We then reduce the on-hand stock by two:
UPDATE inventory SET on_hand = on_hand - 2 WHERE wine_id=1029 AND inventory_id=1;
If the on-hand stock in an inventory row is reduced to zero-which isn't so in this case-we then remove that row:
DELETE FROM inventory WHERE wine_id = 1029 AND inventory_id=1;
Other possibilities may also occur, such as having to manipulate two inventories because the oldest inventory has only one bottle left. These possibilities are discussed in further detail in Chapter 12.
Having reserved two bottles of the wine for shipping, we can finalize the order for the customer. To do so, we need to store the details of the shopping cart entries in the orders and items tables. As discussed previously, by tracking the shopping cart of this user we know it has the
order_id=354 for the dummy
cust_id=-1. We also need to know how many previous orders this customer has made:
SELECT max(order_id) FROM orders WHERE customer_id=1;
If you find the customer previously made two orders, you update the shopping cart order row so that it is now the third order for this customer. Use this statement:
UPDATE orders SET cust_id = 1, order_id = 3, date = NULL, delivery = 7.95, discount = 0 WHERE cust_id = -1 AND order_id = 354;
The shopping cart entry is now a customer order.
date=NULL sets the
date attribute to be the current system time and date. The delivery cost is $7.95, and there is no discount on the order.
To complete the order, we also update the related items row in the shopping cart, which contains the two bottles of wine. Use the following
UPDATE items SET cust_id = 1, order_id = 3, date = NULL WHERE cust_id = -1 AND order_id = 354 AND item_id = 1;
There is no need to update the
We can now confirm to the customer the purchase of two bottles of Curry Hill and ship the order.
This isn't quite the whole picture of purchasing wines or updating the database. In Chapter 6, we return to similar examples and discuss the implications and problems of many users interacting with the database at the same time.