Adding database-driven shipping charge functionality to an online store

Unless your online store deals with digital goods, shipping is a fact of life. Given the complexity of incorporating shipping charges into an e-commerce shopping cart, some store owners may find it tempting to simply absorb the costs. While free shipping may make for very happy customers, it's not an effective long-term solution─the ongoing costs are just too high. This article examines one of the viable alternatives for passing shipping charges onto the customer: a database-driven method that maintains local control. In another article, Retrieving shipping rate quotes in real time, I will review a web service-based technique that gets current ship rates direct from the shipper.

Shopping cart extensions for Dreamweaver support shipping charges to a variety of degrees. Cartweaver uses variations of the database-driven shipping method, whereas WebAssist eCart supports both database-driven and the web service techniques.

Choose your shipping path

Which method you choose is determined both by the client's established shipping accounts, if any, and how you want the shipping charges maintained. If your client does not already have an account with a major shipper like UPS or FedEx that offers web service quotes, the database-driven option is the natural choice. The database-driven method also allows a bit more control over pricing than the formulaic web service technique.

On the other hand, the amount of work involved in setting up and updating the back end for the database approach is extensive. By contrast, the web service method appears quite attractive from a workload perspective, especially after the initial set-up is complete. It's important to keep in mind that with a web service you don't have the precise control over charges granted by a database-driven approach. However, you do have the option to either pass along the exact shipping rate quote returned by the web service or to charge a calculated amount, such as a 10% increase to cover handling.

Setting up the database schema

The first step in enabling database-driven shipping is to establish the proper schema. As you might imagine, there is a great deal of flexibility in how you accomplish this, depending on your shipping requirements. In this section, I'll describe the approach eCart uses in its eCommerce Recipes.

If you've ever looked at a UPS rate chart, you know the basic structure of the shipping elements in your database. The online store needs to be able to charge for shipping based on several factors:

• Distance: Shipping charges generally increase the greater the distance between the shipping origin and the destination.
• Weight: Most shippers charge a base rate for the initial pound plus an incremental rate for additional pounds.
• Service: The type of service—ground, 2-day, or overnight for example—also affects the price; typically the faster the service the higher the cost.

Table 1 shows a sample database shipping schema that addresses all these factors. The database accounts for distance by the ShipState and ShipZone columns; zones are larger areas of the countries encompassing more than one state. Different amounts are entered into the ShipRate column according to the state or zone shipped to. The weight of a shipment is typically calculated in the online store's shopping cart and the charges applied are a combination of the basic shipping rate and the incremental amount. The service factor is handled by the ShipType column. Note that there is a separate database record for each service type within each state. This gives the developer the ability to fine tune shipping costs.

Table 1. Sample database shipping schema with data

Let's look at an example of how this database information might be applied. Let's say that the shopper has ordered goods that weigh 5 pounds and wants 2-day delivery to New York. The calculation would look like this:

Shipping Cost = BaseRate + (Increment * (Total Weight – 1))

Translated into English, the formula reads, "The shipping cost is equal to the shipping base rate (for the first pound) plus the shipping incremental rate, times the total weight, minus the first pound." You must, of course, deduct one pound to compensate for the initial pound, accounted for with the base rate. With this example, the formula would evaluate as follows:

• Shipping Cost = 10.50 + (1.70 * (5 – 1))
• Shipping Cost = 10.50 + (1.70 * 4)
• Shipping Cost = 10.50 + 6.80
• Shipping Cost = $17.30

Where do you get the values to insert into your database shipping table? The best place to start is in the rate sheets used by your client's shipper. You can be as precise—charging different amounts for each zip code—or as broad as desired. You're also free to modify the charges as needed, making them more or less expensive according to the client's wishes. Many stores, like the one in this example, ship from just one location, but larger companies may use multiple shipping centers; in this situation, the database schema will need to be expanded to allow for this possibility.

Two other enhancements are to establish separate tables for the states (or other areas) shipped to and the shipping types (see Table 2). These implementations are great if you want your customers to be able to select their shipping destination and service from select list. It's a simple matter on any page requiring such a list to create two recordsets—one from the state table and another from the services table—and populate the select lists with the results.

Table 2. Shipping services table

If you create these additional tables, be sure to use the lookup function of your database to include their data when crafting your core database shipping table.

Bringing the data into the cart

Now that the data is entered into your database, the next step is to make it accessible to the shopping cart. Because an online store is a multipage web application, the typical technique is to retrieve the information from the database—based on user selections—and store it in session variables. The session variables can then be used in calculations, displayed in a view of the cart, or passed onto to the payment gateway.

Session variables also come into play to store the customer-supplied information required to properly filter the recordsets. You'll recall that of the three criteria for determining a shipping cost, two—shipping destination and service type—are gathered directly from the shopper. The shipping information is input into a form, like the one in Figure 1, before the shipping costs are displayed.

The two select lists—one for the State/Province and the other for the Shipping Method—are both dynamically populated. Recordsets are added to the page to pull in the database-stored information for each. In this example, these form elements are also the ones used to calculate the shipping rate. After the user clicks the Complete Checkout button, server-side code on the next page displayed picks up the state/province and shipping method values and inserts them into session variables. eCart includes a custom server behavior, Set Session Value, that handles this action without coding.

A recordset on the final checkout page uses the shipping type and state session variables to get the right shipping rate. In the case of the example recordset shown in Figure 2, a database view pulls information from three tables: shipping rates, shipping types, and states. Among the information included in the retrieved data are the correct base and incremental charge.

The code uses two more session variables to store the base and increment values. You can then tie these session variables to the shopping cart.

Again, there are a variety of ways to handle the shopping cart manipulations. One technique, used by eCart, is to establish merchandising rules for the charges. Two rules are required, one for the base rate—which is always applied to shippable items—and another for the increment, applied only if the total weight is over a pound. One detail to keep in mind when working with session variables for certain server models, notably ASP and Coldfusion, is that any data stored in a session variable is converted to text; PHP does not have this issue. To be used in a calculation, you'll need to convert the text-based session variable value into a number. Here's how you would convert the base rate session variable:

Displaying the shipping charge

The last shipping-related step is to display the calculated charge to the shopper. Naturally, you want the customer to see all the total due, including all charges and discounts before completing the checkout procedure. Shipping costs are typically grouped with other charges after the subtotal and any applied discounts. Shopping cart displays, like eCart are automatically formatted for you, like the one shown in Figure 3, while remaining customizable.

If you want to repeat the shipping information in another location, you'll need to insert the code to display it. eCart treats any shopping cart rule, such as shipping charges, like other dynamic data and displays it in the Bindings panel; to show it anywhere on the page, you can just drag-and-drop it onto the page.

Where to go from here

This article explored a developer-initiated method for setting your shipping charges within Dreamweaver. A related article, Retrieving shipping rate quotes in real time, discusses the process of getting shipping quotes from the major shipping companies in real time by taking advantage of the web services from UPS and FedEx.

For more web development tutorials, check out the Learn to build dynamic websites page in the Dreamweaver Developer Center.