Site Server - OnlineOfficeSupplies.com

August 1999 

By NDC Group (Sean Phillips and Tracy Das) and Microsoft Corporation (Mukesh Agarwal and RH Prestridge)

Online Office Supplies

In 1998, Online Office Supplies Company of Frederick, Maryland was specifically created to sell office supplies on the Web. Working closely with United Stationers of Des Plaines, IL, Online Office Supplies created an online store, OnlineOfficeSupplies.com to allow consumers to purchase office supplies directly from United Stationers, and to provide a way for customers to save money when purchasing office supplies.

United Stationers is the largest wholesale distributor of business products in North America, offering 35,000 items, including a broad spectrum of office products, computer supplies, office furniture and facilities-management supplies. It has traditionally served 20,000 commercial and contract office products dealers through a distribution network of 65 regional distribution centers. United Stationers provides a high level of shopper service and overnight delivery through its integrated mainframe systems.

The OnlineOfficeSupplies.com site extends this high level of shopper service to online shoppers. It offers over 18,000 of the most commonly used office products, with guaranteed overnight delivery to 98% of the United States. Its low-overhead operation offers greatly reduced pricing to small or medium-sized businesses, which were previously not eligible for volume discounts offered by traditional office-product retailers. This operation also benefits shoppers such as the government, which procures office supplies largely on a lowest-cost basis, and large firms that currently contract purchasing through one local vendor.

A shopper simply chooses from an online catalog, adds items to a shopping cart and pays for the purchase total (which includes taxes and shipping) by credit card or by purchase order. OnlineOfficeSupplies.com sends the order to United Stationers (its supplier) for fulfillment. Online registration gathers shopper information for marketing purposes.

NDC Group of Alexandria, VA, developed OnlineOfficeSupplies.com. It uses components from Microsoft® Site Server 3.0, Commerce Edition, to implement online ordering and commerce interchange processes. NDC created a system that links to mainframe systems at United Stationers for order fulfillment and delivery, to CyberCash for secure credit card processing and to VeriSign for transaction security. OnlineOfficeSupplies.com went online on August 17, 1998.

This paper shows the details of the commerce solution that NDC Group designed and implemented to meet the requirements of OnlineOfficeSupplies.com.

Solution Overview

NDC implemented OnlineOfficeSupplies.com in two phases over a period of 3.5 months:

• Phase 1 implementation included graphics standards and navigation, shopper personalization, product search by category, manufacturer and stock number, credit card processing, United Stationers interchange and e-mail order verification. 

• Phase 2 implementation included tracking purchase history for shoppers, custom-entry pages for shoppers, daily and seasonal special highlighting, database-driven shopping cart holdover, and administrative enhancements to allow viewing of recent financial transactions and product database updates.

Phase 1: Order Processing and Fulfillment

Phase 1 created the initial site with order processing and fulfillment, and Phase 2 added order management and shopper service extensions to the application:

Online Shopping

• Product Selection: Shoppers may browse products by product category and subcategory. After category/subcategory has been selected, summary screens display items with thumbnail images that can be clicked for a larger image and more detailed description of the product. Links enable the shopper to add the selected item to their shopping cart. Shoppers may also browse by manufacturer or stock number, or locate items by full- or partial-text search. 

• Shopping Cart. Shoppers add and subtract items and quantity ordered from the shopping cart as they navigate the site. At any point, shoppers can view and alter the contents of their shopping cart and then resume shopping, or proceed to check out. Allowing a shopper to save a shopping-cart order (i.e. leave the site, return and resume shopping) was implemented in Phase 2. Quick Shop capability allows a shopper familiar with the system to quickly repeat their last order and proceed directly to check out. The Quick Shop option automatically fills the shopping cart with the most recently ordered items. At check out, shoppers can modify selected quantities and items. 

• Showcased Products. An Office Essentials feature allows the shopper to "quick shop" from a list of the most frequently ordered items in the office supply industry. A super sale feature offers a subset of office products that are deeply discounted as a special promotion. A comparison table feature shows prices for standard products sold by OnlineOfficeSupplies.com, compared with those sold by its competitors.

• Shopper Registration. The site registers shoppers automatically when they make a purchase, or when they complete the registration process. Registration is used to present personalized welcome messages and reminder lists of recently ordered items. A "remember me" feature re-registers a shopper who has previously registered but is not recognized by the system.

Check Out/Order Processing

• Check Out/Shopping. After a shopper adds an item to their shopping cart, they may proceed to check out from any point on the site. 

• Check Out. At check out, the shopper may verify or change items ordered and their quantities, and then proceed with their purchase.

• Credit Card Authorization. After verifying their order, the shopper submits credit card information (name on card, billing address, shipping address, expiration date, etc.). An authorization code is generated, and order information gets recorded in the site database. 

• EDI/Order Processing. After credit card authorization, the order gets a unique order identification number and joins a queue for processing at United Stationers. At regular intervals, the United Stationers order processing system receives orders in batches. The confirmation includes an order ID used for order tracking. 

• Credit Card Processing. OnlineOfficeSupplies.com debits the shopper's credit card after receiving shipping confirmation from United Stationers. 

Order Management

• E-mail Order Verification. After the credit card has successfully processed, OnlineOfficeSupplies.com e-mails the shopper an order verification notice, detailing items ordered, item cost, total order cost, an e-mail address for OnlineOfficeSupplies.com shopper service, a shopper service phone number and an order ID and HTTP link for tracking shipment of their order. 

• Order Tracking. The site provides shoppers with the capability to link to UPS's tracking site and monitor order status by using the order ID e-mailed to them. 

Integration with United Stationers

• The requirement to integrate with the United Stationers site for Phase 1 meant that United Stationers Turbolink EDI client software was used for order fulfillment and shipping. This EDI link was later replaced by the Commerce Interchange Pipeline (CIP) component from Microsoft Site Server 3.0, Commerce Edition.

Phase 2: Order Management and Shopper Service Enhancements

Order Management Enhancements

• Organization Purchase Tracking. This enhancement has two components: (1) creating a custom entry page for each non-profit organization, school or large corporate shopper and (2) using the page to track and display orders placed by the organization.

Shopper Service Enhancements

• Seasonal Specials—OnlineOfficeSupplies.com displays special seasonal items (like back-to-school items), similar to those in the Office Essentials listing. 

• E-mail—OnlineOfficeSupplies.com shoppers may sign up to receive regular promotional e-mails. 

• Check out prompt for the Special of the Day—OnlineOfficeSupplies.com can select an item to be offered at a set point in the check out process. If shoppers choose, the item can be added to the order and the check out process continues.

• Save your cart—Shoppers can save their shopping cart and complete the transaction at a later date.

• Order tracking history—This displays order history (order date, shipping date, items on back order, etc.) to a shopper who may have deleted e-mail notification or did not receive an order confirmation e-mail. 

• Monitor Order and CyberCash System log—OnlineOfficeSupplies.com Shopper Support displays the last 15 days of EDI and CyberCash transaction logs. 

Integration with Microsoft Technologies

• In order to provide better reliability and availability of the OnlineOfficeSupplies Web site, Microsoft Cluster Server (MSCS) was implemented. The architecture consists of two Compaq servers configured specifically as database servers using Microsoft SQL Server™ 6.5. One of the advantages of MSCS is that it can detect application or server failure and quickly transition load to remaining servers. As traffic on the site increases, additional servers can be added to the cluster to increase performance to handle the higher volume. MSCS can also be used during routine server maintenance to remove a specific server from the cluster without requiring downtime on the site. 

Specification

This section describes detailed process flows for applications at the heart of the OnlineOfficeSupplies.com solution:

1. Online Shopping, including selecting products and product promotions. 

2. Check out/Order Processing, which includes CyberCash credit card authorization. 

3. Information Interchange (using Commerce Interchange Pipeline) with United Stationers. OnlineOfficeSupplies.com carries out credit card authorization before passing an order to United Stationers, where the order is shipped before being passed back to OnlineOfficeSupplies.com for credit card billing. 

4. Personalization and Tracking. Registering shoppers makes it possible for OnlineOfficeSupplies.com to offer personalized customer services such as order- and purchase-history tracking.

Online Shopping

Upon entering the site, shoppers may select products in four ways: from a main product category, from the store directory, by searching for products by name or type or from any of the product promotion lists.

Products are promoted by way of a price comparison table on the home page, by inclusion in an "Office Essentials" list of popular products, through a "Today's Special" shown at check out time, or by a "Quick Buy" that automatically fills the shopping cart with a repeat of the products ordered when this shopper was last in the store. Shoppers can add products to their shopping cart from any point on the site.

Figure 1 shows an example of the OnlineOfficeSupplies.com home page.

Figure 1 OnlineOfficeSupplies.com Home Page 

Product Selection

A shopper can select from a main product category, from the store directory, by searching for products or from a product promotion:

• By main product category: Selecting one of the five main product categories (Desk Accessories & General Supplies; Filing, Shipping and Warehouse Supplies; Writing Instruments; Computer & Office Machine Supplies; and Paper, Envelopes, Binders & Calendars) enables the shopper to select a product from one of the product subcategories (like Desk Accessories - Plastic). 

• Selecting from the store directory: Selecting the Store Directory reveals the five main product categories and three product-promotion programs (Super Deals, Office Essentials and the Affinity Program). 

• Searching for products: The Product Search icon allows searching across all categories, using full- or partial-text criteria. Online.Office.Supplies.com allows advance search capabilities for shoppers to search by SKU or manufacturer part number. The resulting product lists can be displayed in either price or manufacturer sequence. 

• Product promotion: Selecting an icon like Super Deals reveals a list of products currently promoted (see "Product Promotion" below). 

Personal Profile

Returning shoppers may recall stored profiles by choosing Personal Profile and entering their e-mail alias and password. Online.Office.Supplies.com encourages new shoppers to complete an online form for registration.

Product Promotion

• Super Deals: Lists loss leaders and special deals. These include manufacturer discount items or discontinued items.

• Quick Buy: Allows a shopper to quickly select items from their recent purchase history in the OnlineOfficeSupplies.com database. If a shopper does not have a purchase history, the Office Essentials product list is displayed. 

• Office Essentials: A product listing of the 100 most-frequently purchased office necessities. 

• Special of the Day: This prompts a shopper to add one of the "Special of the Day" products to their shopping cart as they complete the check out process, if they have not already selected the item. NDC created an administration page to allow the administrators of OnlineOfficeSupplies.com to select "Special of the Day" products whenever they wanted. 

Save Your Cart

The "Save Your Cart" feature modified the shopping cart from a previous session using a database-driven application.

In Phase 1 implementation, shoppers lost items displayed in their shopping cart if the shoppers ended their session or didn't shop for over an hour. With the "Save Your Cart" feature, the SQL Server database stores the shopping cart. This feature requires the creation of a new table to store the shopper's ID, each item's ID, and each item's quantity. When the shopper returns to the site, the stored shopping cart automatically loads and shopping can resume.

Shopping Cart

When a shopper has finished shopping and clicks Check Out, the total price of items (item price plus taxes) in the shopping cart is calculated. The shopper may edit the cart's contents, causing the amount to be re-totaled. Once the shopper is satisfied with the cart's contents, Check Out prompts the shopper for a credit card or money card number. (A money card is a card similar to a credit card for the withdrawal of funds from a checking or savings account or both.)

A sample shopping cart is shown in Figure 2.

Figure 2 The Online OfficeSupplies.com Shopping Cart 

Check out/Order Processing

To check out, the shopper completes a check out form and provides a credit card number. CyberCash authorizes the credit card. As part of the e-commerce application, OnlineOfficeSupplies.com sends orders to United Stationers using a Commerce Interchange Pipeline. Once United Stationers confirms the order's shipment, the credit card account is billed.

Check Out Form

If a shopper has previously purchased or has previously registered, their name, company name (if applicable), shipping address, billing address and e-mail address are pre-populated into the check out form. All fields are editable. The shopper must enter credit card information for each order. Credit card information is encrypted in the database until the order is complete. After the order is complete, the credit card number is deleted and is no longer available.

CyberCash Authorization

CyberCash authorizes a shopper's credit card in real time while the shopper is in Check Out. If credit card authorization fails for the purchase amount, CyberCash prompts the shopper to confirm the credit card information or to enter a new credit card. If the card number was entered incorrectly, or is not a valid number, CyberCash prompts the shopper to re-enter the correct information.

Information Interchange

After successful authorization of the credit card, OnlineOfficeSupplies.com forwards order and credit card information to United Stationers, where the shopper's order is pulled, packaged and shipped. The Implementation section gives more detail about the CIP process that facilitates this communication between OnlineOfficeSupplies.com and United Stationers.

CyberCash Billing

Once the shopper's order has been shipped by United Stationers, CyberCash charges the shopper's credit card for the purchase amount. A batch process runs once a day to credit the OnlineOfficeSupplies.com bank account with the day's purchases. 

Purchase Completion

After completing a purchase transaction, OnlineOfficeSupplies.com records the purchase transaction in the personalization database, and sets a cookie on the shopper's browser. A history of past purchases is available online for use in the Quick Shop area of the site.

E-mail confirms shipping and billing information, or to notify the shopper of any order changes.

Figure 3 The CyberCash Interaction Process 

Information Interchange (using CIP)

CIP facilitates order-information exchange from OnlineOfficeSupplies.com to United Stationers. It also confirms that United Stationers received order information and sends order-status information from United Stationers back to OnlineOfficeSupplies.com.

CIP breaks the data exchange process down into configurable stages. Stages include mapping data to a format, such as XML, for transfer over the Internet, digitally signing the data, encrypting the data and sending data over the CIP pipeline. At the receiving end of the pipeline—United Stationers—CIP decrypts the order information, verifies the digital signature, and converts XML data into order information. From there, United Stationers processes and fulfills the order.

CIP queries the database for all orders that have completed Check Out (credit card authorized) and places orders in a flat file in the proper X12 format for transfer to United Stationers. Turbolink client software, provided by United Stationers, transfers order information at least once a day. Turbolink automatically performs error checking and confirms that all orders are transferred. If Turbolink detects an error occurred during the transfer, it sends an e-mail to OnlineOfficeSupplies.com with details of that error. The shopper also receives an e-mail message provided by OnlineOfficeSupplies.com, stating that the order has been delayed. After United Stationers returns a status of complete for each order, United Stationers charges for each order and sends an e-mail to the shopper with the UPS tracking number. 

Figure 4 shows an example of the receive portion of the CIP pipeline.

 

Figure 4 Receiving portion of the CIP pipeline 

Binary to Text

This component reads the binary file received from GEIS (General Electric Information Services) EDI and translates it into a test-flat file for further processing.

Pull UPS

This component pulls UPS tracking numbers and user information from the flat file. This component takes the place of the GETEDIinfo function, which is part of a PERL 5.003 script used for tracking UPS shipping numbers.

Send User Info

This component takes parsed records created from the Pull UPS component and sends an e-mail to individual users with the UPS tracking number.

The following diagram displays the process flow for the information interchange between OnlineOfficeSupplies.com and United Stationers.

Figure 5 The Information Interchange Process 

Personalization and Tracking

If a cookie is detected when a shopper enters the site, OnlineOfficeSupplies.com retrieves shopper preferences and invokes the shopper's personalized profile. Otherwise, the shopper is invited to register or to identify themselves as a returning shopper. The process is shown in Figure 6.

Figure 6 The Personalization Process 

The personalization system presents a custom message to each registered visitor that includes the person's name.

Registration

All shoppers who complete a purchase are automatically registered and added to the personalization database. A visitor may also click Remember Me and complete a registration form. A cookie provides identification needed to determine if a visitor has previously registered.

If the shopper visits the site with no cookie or a new browser, the shopper can click Remember Me and get the cookie reset by providing an e-mail address and password. If the shopper forgets the password, they may either have OnlineOfficeSupplies.com e-mail their password, or re-register.

Order-Tracking History

Order-Tracking History allows a registered shopper to view the status of an order by selecting a specific order from a list of all recent purchases with OnlineOfficeSupplies.com. Order status information includes:

• Date order was made. 

• Whether the order is in process or if it has been shipped. 

• Items on back order. 

• Date order was shipped. 

• Order delivery date. 

Organizational Purchase Tracking (the "Affinity Program")

OnlineOfficeSupplies.com offers non-profit organizations, schools and large organizations the ability to track purchases made by members or employees, for example, by giving a listing of recent orders or by showing a field containing total-order dollar amounts to date. Online.Office.Supplies.com tracks purchases for all shoppers in an organization who enter OnlineOfficeSupplies.com by way of a custom page provided by OnlineOfficeSupplies.com, rather than through the public home page. Fields on the custom page contain tracking information.

If a shopper enters the site from the public home page, the shopper cannot be included in a particular organization's purchase tracking, because only shoppers who enter the site from the organization's unique custom page are tracked. The shopper experiences no difference in site navigation, registration, purchasing or the check out process since pages for organizations and individuals shoppers are basically the same.

Implementation

NDC implemented OnlineOfficeSupplies.com as a custom application built on a foundation of Microsoft Active Server Pages (ASPs), Microsoft Internet Information Server 4.0, Microsoft Windows NT® Enterprise Edition and Microsoft SQL Server 6.5. Its functionality was fashioned before Site Server Commerce Edition became available.

Site Server 3.0's CIP replaced an earlier EDI link with United Stationers in Phase 2 of the implementation.

NDC designed the application to fit an 800X600 screen, and to work in any browser from Microsoft Internet Explorer 2.0 and up and Netscape Navigator 2.0 and up. Figure 7 shows the logical architecture for OnlineOfficeSupplies.com.

Figure 7 OnlineOfficeSupplies.com Logical Architecture 

Physical Architecture

OnlineOfficeSupplies.com's physical architecture consists of two Web servers and two database servers connected using Microsoft Cluster Server (MSCS) that share-replicated information hosted at UUnet. The physical architecture is shown in Figure 8.

Figure 8 OnlineOfficeSupplies.com Physical Architecture 

Table 1 OnlineOfficeSupplies.com Server Configurations 

SERVER	INFORMATION
OnlineOfficeSupplies.com Affinity Sites Server	Compaq 6800/Dual 300 MHz processors/512 MB of RAM/9GB of storage.
Replication Process Server (1)	Compaq 6800/Dual 300 MHz processors/512 MB of RAM/9GB of storage.
Replication Process Server (2)	Compaq 6800/Dual 300 MHz processors/512 MB of RAM/9GB of storage.
Franchise Server	Compaq 6800/Dual 300 MHz processors/512 MB of RAM/9GB of storage.

Development Resources

Table 2 shows the resources used in building OnlineOfficeSupplies.com. Seventy percent of the effort involved was in SQL stored procedures, Visual Basic objects and ASP page development.

Table 2 Development Resources 

Skill	FTE	Months	% Effort
VB/ASP Developers	4	3	50%
Data Modeler	1	2	10%
Tech Analyst/Business Analyst	1	3	10%
Graphic Artist	1	2	10%
Testers	3	2	20%

Code Components

CIP

NDC chose CIP because it is easier to automate than EDI, manages electronic communication with distributors, resellers and suppliers and has multi-lingual capabilities. It also allows:

• Trading partners to have individual profiles. 

• Targeted online advertising and marketing.

• Personalized promotions. 

• The ability to analyze purchase patterns. 

• The ability to analyze site usage patterns. 

• The ability to build upon existing component foundations. 

• The ability to write additional reusable components. 

• Use of Java script or Visual Basic© script without being limited to Visual Basic, Visual C++® or Visual J++®. 

NDC installed CIP with Microsoft Site Server 3.0, Commerce Edition. CIP ties into the existing database schema, ties into all existing order processing forms using script components and pulls item information and order totals from an existing shopping cart.

Organization Purchase Tracking (Affinity Program)

NDC added administration screens to allow OnlineOfficeSupplies.com to track new organizations, to associate a limited amount of customization to the entry screen (an image or change in banner/text message) and to view a report of sales associated with an organization.

The main page displays the total amount purchased (year-to-date) by that organization from OnlineOfficeSupplies.com. An administration page calculates how much money an organization has spent, how many purchases it has made and the average purchase amount.

Some coding effort is needed from NDC or a trained HTML programmer to complete each custom Affinity site by taking a client's current site and customizing it so that Affinity-site features appear.

Save Your Cart

A test on the home page checks for a saved shopping cart. An SQL Server query, using the current shopper's shopper ID, checks for a saved shopping cart in the database. If found, Save Your Cart loads the contents of the saved shopping cart into the new session's shopping cart.

Seasonal Specials

Seasonal Specials uses a general-items-listing page incorporating the functionality of the Super Deals page already in place on the OnlineOfficeSupplies.com site. The Super Deals listing is redirected through this new general listing page. The general-listing page consists of a unique graphic and a listing of items designated as special by OnlineOfficeSupplies.com. Graphics are stored in a special subdirectory. If there is no associated graphic, the page is titled with alternate text. Creating a new seasonal special requires a new graphic and some administration work, like reworking HTML code making sure that pricing is correct.

The administration of these pages is handled through the administrative site where OnlineOfficeSupplies.com creates, deletes, and modifies active status of specials, as well as setting the overall margin on a special. The adjustment for the margin of the item is global for all occurrences of that item throughout the site.

NDC had to modify the database to meet this requirement, since a new table was needed to store the special sale type, sale ID, its associated graphic and its alternate text. A second table stores item entries consisting of an item's ID, prefix, stock number and sale ID. The Super Sale table data is merged into this new table, and subsequent seasonal special items are then added.

E-mail Lists

OnlineOfficeSupplies.com gives shoppers the opportunity to sign up for e-mail lists during the registration process. A subscribe/unsubscribe include file, on the User Registration page, allows the shopper to add or subtract themselves from a mailing list by selecting a checkbox in which they wish to alter membership. Subscribers to e-mail lists cannot reply to the e-mails, and cannot contact other subscribers to the lists, a preventive measure against spamming. An administrative page allows the list administrator to add and subtract from e-mail lists.

Security

EDI*Express to EDI*PC Security

The EDI*Express Service enables business-to-business transfer of electronic data by computer-to-computer communication. EDI*Express is a mailbox service provided by GE Information Systems (GEIS). OnlineOfficeSupplies.com connects to its mailbox through GEIS's secure Value Added Network, or VAN. VAN only allows OnlineOfficeSupplies.com to send and receive information with its mailbox. VAN handles all transfers of data from United Stationer's mailbox to OnlineOfficeSupplies.com's mailbox, isolating the two entities one from the other. Access to these mailboxes is restricted by user name and password.

EDI*PC is the EDI client used to receive data from the EDI*Express mailbox service. EDI*PC was replaced by CIP to receive data from United Stationers's mailbox at EDI*Express. EDI*Express encrypts data in the OnlineOfficeSupplies.com mailbox. EDI*PC, which is kicked off by PERL scripts, issues a "receive". Upon completing the "receive" of the encrypted flat file, EDI*PC decrypts the file and saves it to a local location. CIP communicates with the EDI*Express mailbox to issue a "receive" and decrypt the data upon receipt. Once the file has been pulled, it is then sent through the CIP for processing. The pipeline components replace the PERL script.

User Security 

Only authenticated shoppers or shopper service representatives can place and review orders. An encrypted password protects shopper profiles. All confidential information, such as shopper personal and credit card numbers, is stored only for verification and charges, and is not centrally or readily available. SSL (HTTPS) enforces secure data communication.

All users logon locally to the NT box at UUnet through the IUSER OnlineOfficeSupplies.com anonymous Internet account. The logon is transparent to the user if a cookie is present on the shopper's machine. If a cookie is not present, the user is prompted to register or enter their name and password. This information is validated against the user database by way of SQL.

The Windows NT administrator checks event logs to monitor whom successfully or unsuccessfully enters the site.

User Administration Security 

Access to the administration site is granted to OnlineOfficeSupply.com employees and NDC by way of IP domain restrictions, handled by UUnet. Security for logging on to the administration site is handled through Windows NTLM Challenge/Response and NTFS.

Data Architecture

Table 3 shows the data architecture tables in the OnlineOfficeSupplies.com solution

Table 3 Data Architecture Tables and Components 

TABLES	DESCRIPTION
Banner	Displays OnlineOfficeSupplies.com's logo. Similar to a newspaper banner.
Billboard	Advertises OnlineOfficeSupplies.com specials.
Item Description	Short item descriptions of products sold by OnlineOfficeSupplies.com. Linked to Items.
Main Category	Lists overall categories within the OnlineOfficeSupplies.com page. Linked to Upper Category.
Upper Category	Table of categories beneath Main Category. Linked to Upper Category and Category.
Category	Table of categories beneath Upper Category. Linked to Upper Category and Items.
Items	Items sold by OnlineOfficeSupplies.com. Linked to Sale Items, Margin Type, Office Essentials, Item Description, Category, Vendors, Checkout Prompt and Order Detail.
Office Essentials	List of essential office supplies—like pens, paper, paperclips, etc. Linked to Office Essentials Categories and Items.
Margin Type	Table of margins—price differences—among OnlineOfficeSupplies.com and its competitors. Linked to Margins, Office Essentials, Sale Items and Items.
Margins	Table of margins beneath Margin Type. Linked to Margin Type.
Office Essentials Categories	Table of Office Essentials linked to Office Essentials. Linked to Office Essentials.
Sale Items	List of items currently on sale at OnlineOfficeSupplies.com. Linked to Items and Sale type.
Sale Type	List of sales types. Linked to Sale Items and Affinity Type.
Cart Status	List indicating if cart is active or inactive or on hold. Linked to Shopping Cart.
Shopping Cart	List showing the contents of the shopping cart. Linked to Cart Status.
Vendors	Table containing the vendors OnlineOfficeSupplies.com uses. Linked to items.
Checkout Prompt	Prompts used by customers when they are done shopping. Linked to Items.
Order Detail	Details created by customers as they enter orders. Linked to Items and Order Header.
Affinity Type	Used in the Affinity program, which gives commissions to groups and organizations that promote OnlineOfficeSupplies.com on their home sites. Linked to Users and Sale Type.
Users	List of users in OnlineOfficeSupplies.com. Linked to Affinity Type, Sweepstakes, Order Header and User Address.
Sweepstakes	List of sweepstakes currently offered by OnlineOfficeSupplies.com. Linked to Users.
User Phone	Lists of customer phones. Linked to Phone Type.
Phone Type	Lists of area codes used by customers. Linked to User Phone.
User Address	Lists of customer addresses. Linked to Users, Order Header and ZIP Codes.
Order Header	Lists or order numbers. Linked to Users, User Address, UPS Num(ber), CC (credit card) Info, and Order status.
CC Info	Lists of credit card numbers used by customers. Linked to Order Header.
ZIP Codes	Lists of ZIP codes used throughout the United States and its territories. Linked To User Address.
UPS Num	Tracks orders shipped by UPS. Linked to Order Header.
Order Status	States whether an order is active, inactive or on hold. Linked to Order Header.
List Type	Table of various email lists used in the OnlineOfficeSupplies.com application. Linked to Email List.
Email List	List of customer e-mail addresses. Linked to List Type.
Counter	Lists the number of times a customer has visited OnlineOfficeSupplies.com.
Compare	Lists comparisons of prices between OnlineOfficeSupplies.com and its competitors.
FAQ Cat	Lists frequently asked questions made by customers. Linked to FAQ QA (Quality Assurance).
FAQ QA (Quality Assurance)	Tracks questions made by customers. Linked to FAQ Cat.
Messages	Tracks messages left by customers.

Figure 9 shows the data architecture for OnlineOfficeSupplies.com. A directional arrow shows a "has a" relationship between two items. For example, Category has an Upper Category, which in turn has a Main Category; Order Header has an Order Detail, which in turn has an Item.

Figure 9 OnlineOfficeSupplies.com Data Architecture 

Development Tools

Table 4 Development Tools 

Name of Product	Purpose
Microsoft Access	Database query tool
Adobe PhotoShop	Used for Web graphic development and enhancement
CyberCash 3.203	Credit card validation
CyberCash InstaBuy Agile Wallet 2.0	Wallet technology
GEIS EDIPC	EDI client software
Microsoft Internet Explorer 4.0/5.0b	Supported browsers
Microsoft Active Server Pages	HTML and data-presentation coding
Microsoft Excel 98	Reports/issues log/change management log
Microsoft FrontPage® 98	Quick HTML page design, publishing tool
Microsoft Internet Information Server 4.0	Web server software
Microsoft Windows NT Server Enterprise Edition 4.0 (NTFS and NTLM)	Operating system
Microsoft Project	Core project management tool
Microsoft Site Server 3.0 Commerce Edition	Commerce server software
Microsoft SQL Server 6.5	Database software
Microsoft Visual SourceSafe™ 5.0	Source control and maintaining revisions
Microsoft Visual Studio 6.0	Core development package
Microsoft Office 98	Reports/issues logs/change management logs
Perl 5.003	Used for backend server processing
SQL Server Enterprise manager	Used to create stored procedures
Trilogy BuyingChain 2.0	Buying chain purchasing process
Visio	Used for technical drawings
Microsoft Visual Basic 6.0	Used for backend server processing development
Microsoft Visual InterDev™	Development and maintenance of Webs
WebTrends	Detailed site statistics and reports

Site Administration

Monitor CIP and CyberCash System Logs: NDC has provided an administration screen to view the last 15 days of the CIP and CyberCash log files. After the fifteenth day, the CyberCash and CIP log files are deleted from the system and can be recovered by OnlineOfficeSupplies.com from daily UUNet tape backups.

Animation Graphic: This page allows an administrator to enter up to five SKU numbers and display an associated graphic. This allows the lookup of a particular graphic in case the graphic has to be resized to properly fit into the 100x110 area allotted on the opening page. The selected SKU must have a graphic associated with it. The administrator can also modify the graphic associated with a particular SKU number.

Header/Footer Message: This page allows the administrator to change text displayed on the opening page, but not the text format.

Scrolling Banner: This page allows an administrator to edit text in the scrolling banner on the opening page, but not text format. The administrator can specify "clickable" actions for each scrolling-banner portion (like links to pages, sending e-mail, etc.)

Items: This page is the entry point to add or delete Main Categories, Sub Categories and related items. An administrator can also modify margins associated with Main Categories, Sub-Categories and items, and add items or edit text associated with that item.

Sale Items: This page allow an administrator to add or delete seasonal specials, as well as to edit items and margins associated with a special.

E-mail List: This page allows the administrator to add and delete mailing lists.

Send E-mail to List: This page allows the administrator to send e-mail to mailing lists.

Lessons Learned

Scalability

NDC designed the initial implementation of OnlineOfficeSupplies.com with the majority of shopper-and-site information stored in the session, residing in server-side memory. NDC assumed that repeated hits to the database would slow down site performance.

In practice, having information stored in the session caused many shoppers to time out, apparently because the repeated looping on the session variables was very slow. Instead of storing information in the session, site information was made entirely database-driven, based on a unique shopper ID, stored in a cookie and in the URL. Having information in the database improved site speed, reliability and scalability. It also makes site code that much more reusable.

Use of CyberCash

NDC learned another lesson concerning the use of CyberCash as the credit-card- validation-and-charging service. At times, NDC found CyberCash's servers unreachable, which made order check out impossible for many shoppers since they could not get credit cards authorized.

To resolve this, NDC moved CyberCash from being a real-time process to a back-end process. NDC designed the system so that it securely stores the shopper's credit card information upon an order being placed (after the system performs a LUHN-10 check on the numbers to make sure they are true credit card numbers). Once an hour, a batch process runs, which authorizes the shoppers' cards. If card authorizations fail, e-mails are sent to shoppers, stating there was an error with their cards and asking them to go to the site to re-enter the correct credit card information. CyberCash places the items back into the shopping cart so that shoppers do not have to reselect the items.

Separating Business Logic

NDC also learned the benefits of keeping all business logic separate from ASP pages.

When NDC first designed the site, NDC put all logic on the ASP page itself. Through site revisions, NDC was able to transfer business logic to stored procedures and COM (common object model) objects. This greatly improved site performance, and made much of the logic reusable, in addition to making the site itself more scaleable.

Source Code Control

NDC discovered the importance of using a source-control application throughout development of the site. NDC learned its necessity when it discovered problems in newly designed pieces for the Web site. Instead of having to pick apart the new code, NDC chose simply to roll back the source-control version, fixing the error.

Browser Support

NDC learned that supporting a wide range of browsers was more complicated than first anticipated. NDC originally designed the site to work with only Netscape and Internet Explorer 3.0 and newer versions; however, requirements changed after initial launch, and NDC had to make the site work for all browsers, including browsers below 3.0.

NDC discovered that some browsers were unable to support SSL and JavaScript. In order to maintain a navigable site using any of the available browsers, NDC created browser-specific versions of affected pages, which execute certain potions of code based on the particular browser used.

For More Information

For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Center at (800) 563-9048. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information via the World Wide Web, go to the https://www.microsoft.com site.