| Introduction For an e-commerce Web site, a shopping basket is a crucial part of the equation. Having a fully functional and easy-to-use shopping basket can be the difference between a shopper completing his or her visit with a purchase from your site, or simply adding to the list of abandoned baskets on the Internet. The days of the HTML form as shopping basket are long gone; your shoppers expect more. ISPs and business owners know this, and that is why, today, most ISPs will charge an extra fee to add a shopping cart to your hosted Web site. This article shows how to create your own shopping cart relatively easily. This article uses the terms "shopping cart" and "shopping basket" interchangeably. Also, to define "shopping cart," I am referring to the aspect of the Web site that allows shoppers to enter products and modify them in their cart. This does not include the checkout process, which in many cases includes on-line credit-card verification and billing procedures. The Problem If an ISP hosts your e-commerce site, your shopping cart options have traditionally been limited to: - Paying extra each month for an ISP-provided shopping cart program such as Express Technologies' CartExpress.
- Purchasing a commercially available third-party shopping cart program (usually a DLL) and paying your ISP to install it on a dedicated server, or
- Using a HTML form to collect shoppers' desired products and orders.
In each of these cases, you either have an added expense to your hosting fees, or you have a less than optimal order-collection system.
The Solution Creating a shopping cart application with all the functionality of the commercial options, plus more, is relatively painless. Give your shoppers the ability to create and save different baskets for future reuse, for example. You will get a solution that can scale to meet the demands of all but the most traffic-heavy sites on the Web. All of these use features that come standard with most hosting plans today: ASP, SQL Server, and Open Database Connectivity (ODBC).
Required software To create this basket application, the following software needs to be in place: MS SQL Server 7.0 or higher, IIS 4.0 or higher, and ADO 2.0 or higher. (Download complete source code for this article)
Overview of the Shopping Basket The shopping basket is composed of two SQL tables that store the basket data, stored procedures that interact with the tables to insert, retrieve, or modify data in them (the back end), and three ASP pages on the Web server (the front end) that simply contain logic to make the database calls. In-depth analyses of these layers follow below.
Back-end (Database) Design Tables
At its core, every shopping cart program is made up of a series of calls to the database to save, retrieve, or modify data. Our shopping cart is no different. The database diagram for the tables is shown below. 
Figure 1: Database diagram for the shopping cart.
The guiding principle for the above design was to make the shopping cart as extensible as possible, as well as to have the database do as much of the housekeeping as possible. It is amazing how many database designers neglect to fully take advantage of the built-in capabilities of their Relational Database Management Systems (RDBMSes) to minimize data manipulation errors. This design takes advantage of SQL Server's ability to enforce referential integrity, constraints, identity columns, etc, freeing the developer to worry only about shopper-specific issues. It is advisable to have at least two tables that comprise your shopping cart - as opposed to having one, wide, un-normalized table. This allows you to better enforce normalization in your design by not repeating data. It also allows you more room for extensibility of the shopping cart. Some things that should stand out from the design above: - The shopper_id column is not set as a primary key, or a unique identifier. This allows a shopper to have more than one basket at any time. Which basket is displayed is determined by the default_basket column, which has a bit data type (meaning it only allows 1 or 0). I chose to use a bit data type to conserve server resources, but a tiny int data type would suffice.
- The primary key is set on the basket_id column and is foreign-keyed in the basket_detail table to ensure referential integrity. The basket_id column is also an identity column in the basket table with a default value of 1000 that is incremented by 1 for each new basket created.
- The basket_detail table has a composite primary key using the basket_id and sku columns. (A composite key is a key created by using more than one column in a table). The reason we want a composite key is to enforce the requirement that no one basket can contain more than one row of the same product (sku).
- The date_created and date_modified columns in the basket table are a good design practice because they not only tell you when the basket was created, but when a shopper last modified his basket content. This can be an excellent audit trail that opens opportunities to add new date-sensitive features to the shopping cart (like sending e-mail reminders to shoppers whose baskets have not been touched for one week, etc...).
The SQL script for creating the described tables is shown below. 
Figure 2: SQL script for creating shopping basket tables.
Stored Procedures The shopping basket is composed of nine stored procedures. Five of these are considered crucial to the core basket functionality, while the remaining four allow the added multiple-basket functionality. I cannot explicitly type out the code for each of them here, so I will follow this paragraph with their names, a brief discussion of what they accomplish, and what they return to the caller. Each of these has been scripted out and is part of the download file for this article. Sproc_addToBasket:
This stored procedure (SP) adds a new product to the shopper's default basket. A return code of 0 means success, while 1 signifies an error in the operation. Sproc_createBasket:
This SP checks to see if a shopper has an existing default basket. If none exists, it creates one and returns the basket id (which must be greater than 1,000) to the calling procedure. This SP is called only by the SP sproc_addToBasket before the product add operation happens. A return code of 1 implies an error. Sproc_getBasket:
This SP returns a recordset of the shopper's default basket for display on the browser. Sproc_deleteBasket:
This SP is responsible for deleting the shopper's entire specified basket. It returns 0 for success and 1 for failure. Note that this SP uses a transaction. The reason for this is simple - you want to minimize data corruption by ensuring that either all the basket information is deleted from both the basket and basket_detail tables, or nothing happens. Sproc_updateBasket:
This SP updates the shopper's default basket to register any quantity changes, etc. It returns 0 for success or 1 for error. The SPs that follow are part of the added basket functionality and are considered noncritical. That is, the core basket functionality would be intact if they were not available. Sproc_getSavedBaskets:
This SP returns a recordset of all of the shopper's saved baskets to display on the browser. Sproc_makeDefaultBasket:
This SP is responsible for changing which basket is displayed as a shopper's default basket if that shopper has saved baskets. It returns 0 for success or 1 for error. This SP also uses a transaction to ensure that there is no chance of ever having more than 1 default basket for a shopper at any time. Sproc_saveBasket:
This SP is responsible for saving a shopper's default basket if the shopper decides to do so. It simply sets the default_basket flag to 0 and writes the shopper-specified basket name to the basket table. It returns 0 for success and 1 for error. Sproc_createGUID:
For virtually every Web site that users interact with, there needs to be a mechanism for uniquely identifying each visitor. This SP provides that mechanism by using the SQL Server NEWID() function to return a 32-character Globally Unique Identifier (GUID). This is then written to the shopper's cookie on the Web site if the shopper does not already have one.
Front-end Design ASP Pages The shopping basket is comprised of three ASP pages: two browser-viewable and one include file. These pages and their functions are described below. Basket.asp:
This is the page that the shopper interacts with when using a basket. An image of what the shopper sees is shown below. 
Figure 3: A multifeatured ASP shopping cart.
The basket.asp page also has three main functions: - It receives and adds products from the product page to the shopper's basket.
- It processes various actions requested by the shopper from the basket form, such as updates, deletes, etc.
- It displays saved baskets, if any, and provides functionality to let the shopper manage them.
A shopper can also flip between which basket is shown (default basket) from among the shopper'ss saved baskets. Note that the basket.asp page also has disabled session state and is enforcing explicit variable declarations by having the line "Option Explicit." Forcing yourself to explicitly declare variables is a good programming practice because it can prevent against inadvertently reusing or reassigning a variable with the wrong data. Disabling session state by having the statement "enablesessionstate = false " at the top of the page can buy added speed for your page since the server does not waste time trying to account for session management. Save_basket.asp:
This page does exactly what its name implies: It allows the shopper to save a default basket to the database. This comes in handy if the user wants to start a new basket, but would like to preserve what is in a current basket for future purchase. Basket_util.asp:
This page is simply a collection of functions and subroutines that make database calls or display HTML messages. These functions and subroutines are all called from the basket.asp page. It is generally good design to break out sections of code into subroutines that you can then call. It makes for cleaner code, much easier maintenance, and facilitates code reuse.
Putting It All Together Sewing all parts of the application together to produce a single, coherent piece is fairly straightforward. You will notice that most of the business logic behind the basket is placed in the stored procedures. The advantage of this is twofold: - Your application is much easier to migrate to a new platform (or ISP), if you so desire.
- You reduce network traffic by passing raw parameters to the database, having it do the processing, and simply returning the appropriate message back to your calling function.
Error Handling By having "On Error Resume Next" near the top of the basket.asp page, you ensure that the server will continue to process the script even if it encounters an error. This provides a way to manually trap for errors that might occur and presents a more user-friendly error message if an error does occur. This is a much better practice than having your shoppers see a cryptic ODBC error message on their browser.
Conclusion You have seen it is relatively painless to create and add a fully functional, multifeatured ASP-based shopping cart to your Web site. You do not have to spend the money to buy any commercial basket software. The solution shown here is also highly modular. This means, provided your Web site currently runs on IIS (supports ASP) and you have a database on the back end, you can simply plop the shopping basket into your existing Web site with very little modifications to the site's existing source code. I realize there are different ways this solution could be designed and implemented to achieve a similar result. As such, I welcome any and all feedback or suggestions on how you would have designed/implemented the solution differently from what was shown.
About the Author Manny Agrinya is a Microsoft Certified Solutions Developer from New Jersey. He enjoys riding or tinkering with his motorcycle, and when not doing that, he is spending time in front of a computer - coding, or learning more about Linux and open-source software. He can be reached by email at emm_ag@yahoo.com or www.agrinya.com.
|
