16.1 Functional and System Requirements

Before we built the winestore, our fictional client told us what it should do and what features they wanted. Let's take a look at the scope and aims of the winestore that would typically be gathered from interviews, studying workflow, and so on.

Hugh and Dave's Online Wines is a fictional online wine retailer. The winestore sells wines from wineries that are located in regions throughout Australia. The winestore is open to the public: anonymous users have limited access to the application, and users can make purchases if they become members.

Any user with a web browser can access the site, browse or search for wines, and view wine details. The winestore carries thousands of wines, and the details of each wine include its name, vintage (year of release), the winery that makes it, grape varieties, and, in some cases, an expert review of the wine.

An anonymous user can search for wines and add selected wines to a shopping cart. However, to purchase wines, a user must be a member and log in using their username and password. Membership is open to all users and the membership application process collects details about the customer in the same way as at most online sites. When placing an order, the member must provide their credit card details, as well as any shipping instructions. An order is shipped immediately and a confirmation sent by email.

The winestore doesn't do much more than manage members, wines, and ordering. It doesn't have a reporting module, an administration interface, or any content management features. It is not designed to be a fully functional application but only to illustrate most of the fundamental techniques discussed throughout the book.

The next section outlines the requirements of the winestore in more detail.

16.1.1 Requirements List

Here's a summary of the functional and system requirements:

• The online winestore is an e-commerce site to sell wine.

• The application doesn't manage accounting, stock control, payroll, ordering, and other tasks.

• Users may select wines and add them to a shopping cart. Users may purchase the items in their shopping cart for up to one day after the first item is added to the basket.

• Shopping carts are maintained for 24 hours, after which they're emptied. If a user quits their web browser or logs out, then the cart is emptied.

• Users have only one shopping cart each and may empty their carts at any time.

• Users of the site may be anonymous and can remain anonymous until they decide to purchase the items in the shopping cart.

• To purchase items in a shopping basket, the user must log in to the system. To log in, a user must be a member.

• To get a membership, a user must provide at least his surname, first name, a title, an address line, a city, a Zip Code, a country, his birth date, an email address, and a password. The email address is used as the user's login name. The user may also optionally provide a middle initial, a state, and a telephone number.

• When a member purchases wines, she must provide credit card details. The card details are associated with the order. An order may also have a delivery note that is directed to the delivery company; for example, a note might indicate to "leave the wines at the back door of the house."

• When a member purchases wines, his order is kept in the database.

• Wines are classified into broad types of red, white, sparkling, sweet, and fortified. Wines also have a name, a vintage (year of release), and a description; descriptions are optional free-form text that are typically a review of the wine similar to that found on the label.

• Wines are made with different grape varieties, including Chardonnay, Semillon, Merlot, and so on. A wine can be made of any number of grape varieties, and the ordering of these grape varieties is important. For example, for a wine made of two varieties, Cabernet and Merlot, a Cabernet Merlot is different from a Merlot Cabernet.

• Users may browse wines at the winestore by broad type (red, white, and so on) or wine region.

• Wines are produced by one winery. Each winery has a name.

• Wineries are located in a region. A region is an area (for example, the Barossa Valley in South Australia).

• A shopping cart is an order that isn't yet associated with a customer. Each order contains items. It can be converted to a completed order after the user logs in.

• Each item in an order is for a particular wine, a quantity of that wine to be purchased, and a price per bottle. The price of the wine is always the cheapest available inventory price.

• The quantities of wines in the shopping cart can be updated by the user, and items can be removed from the shopping cart by setting their quantity to zero.

• The wines available for sale are stored in an inventory. Each inventory record is unique to a particular wine. The inventory record contains the arrival date of the stock, the quantity available, and a per-bottle price. There can be several inventory records for a wine, representing different shipments that arrived at the winestore on different dates or that have a different price.

• The user will always be given the lowest advertised prices for each wine. When a user adds a wine to her shopping cart, she is guaranteed this price.

• A user can purchase only wines that are in stock.

• When a user converts his shopping cart to an order, the application checks for sufficient inventory to complete the order. If insufficient wine is available, the user is alerted, and the quantities in the shopping basket are updated; this situation can occur if a user adds more wine to his basket than is available, and this can happen when many users shop for the same wine at the same time.

• When sufficient inventory is available to complete an order, the quantity of wine in the inventory is reduced as the order is finalized. The inventory reduced is always the oldest inventory of that wine.

The process of modeling these requirements with relational database entity-relationship (ER) modeling and converting this model to SQL statements is the subject of Appendix E. The winestore ER model can be found in Chapter 5.