Buying a Bottle of Wine from the Winestore

In this example, we consider the steps required to buy a bottle of wine. Again, assume that there is only one user reading or writing data with the DBMS. The complete process-implemented as part of the winestore web database application-is described in Chapter 12.

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 cust_id=-1, an order_id=354, an item_id=1, 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;

A GROUP BY 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 statement:

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 wine_id, price, or qty (quantity).

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.