1 Introduction

This paper describes the development of a large multi-vendor e-commerce web site in detail.

There are many e-commerce web sites in the internet, generally one for each large company. These sites sell items from that company, and usually don't interact with each other very much. Furthermore, if you want to buy a specific type of item, you generally need to find a site that specializes in that type of product.

Currently, there are few places you can go on the internet to browse items from various vendors, there are even fewer sites where these vendors are smaller businesses, such as what you would find at a local craft fair. This is because the cost of entry to selling items on the web is rather high, both in monetary costs, and technical know how. We hope to lower these barriers, and give the smaller business a chance to sell on the web on a professional looking web site. Along with this comes the ability for a consumer to browse items from different origins in one central web site.

The general idea of the site is quite simple. A customer will order an item, that order will get sent off to the vendor, and then the vendor will fill the order. The system keeps track of the order by maintaining the state that the order is in.

The first state is when a customer browses the items for sale, chooses one (or more) and places an order. The order now goes into an "Ordered" state, and the customer's credit card is verified and charged. The order will remain in this state until the vendor selling the item reviews it and either accepts or rejects it.

If the order is rejected, the customer's credit card is refunded and the customer is informed. If the order is accepted by the vendor (Which should happen most of the time) the order goes into an "Accepted" state. It will remain in this accepted state until the vendor ships it, and tells us he shipped it, possibly with a tracking number.

The order will then go into a "Shipped" state. The customer will be notified to tell the customer it has been shipped. The item will remain in a "Shipped" state for two weeks. During this time the customer may come back to the site and say they got the item and it is acceptable, which means the order would go into a "Complete" state and the vendor will be paid. The customer may also come back and say that they have not gotten the item yet. In this case, the item will remain in a shipped state for another two weeks. The customer can also reply with a "Not Acceptable" in which case the administrators of the system would have to become involved to clarify any problems between the vendor and the customer. If the item reaches the end of its shipped period without feedback from the customer, we assume the customer received the item, mark it complete, and release funds to the vendor. This sales cycle can be seen in Figure 1 of appendix A.

This paper is organized as follows. The website is described in detail through section two. Section three talks about personalization and how it can be used. The fourth section describes what we did in terms of personalization. The fifth section explains much of the specific design for this project. The sixth section runs through the various implementation issues that were encountered, and the seventh section discusses some real world issues in getting the site up and running.

This project combines traditional e-commerce techniques with personalization techniques, and applies those to a multi-vendor website.

2. The Website

The development of the web site was an iterative cycle with several stages of development. After every stage of development, we had people not involved with the project navigate the pages as we observed their actions. This helped us to find bugs, and places where the user interface was not clear.

2.1 Customers

The most important users of the web site are the customers, and therefore we will describe the interface provided to them first. Upon visiting the site, the customer is presented with a home page that lists what the administrators of the site would like new users to see. If the user is a returning user, they are presented with a personalized home page, more about that can be found in the "Personalization" section below.

On every page there is a search function so the customer can search through the catalog. The index consists of a keyword-ID and an inverted index table. The keyword-ID table lists all of the keywords that could be found in any of the items' names or descriptions. It maps the keyword to an ID that can be referenced in the inverted index. The inverted index maps each keyword to one or more items. Since the inverted index is organized by the word and not the document, it is much more efficient to find all of the documents that contain a particular word. Words that occur commonly, but don't convey meaning (called stop words), such as "and", "a", "the", are excluded from the index. The index contains an Inverse Document Frequency (IDF). Where the IDF of keyword i is equal to:

N is equal to the number of items in the database, and DF is the number of items that contain the keyword.

To rank the pages we use a TfxIDF search and ranking algorithm. The algorithm gives us scores that represent how close a query is to a document. It does this in vector space. The equation is as follows:

TF_ij is the term frequency, or the number of times the term appeared, for a keyword in a specific document, and Tf_i-max is the maximum term frequency of any keyword in the document. Q represents the set of words in the user's query. The IDF is the previously calculated value. This method gives higher weights to to keywords that are in shorter item descriptions [17]. This approach to searching is similar to the one described in "A World Wide Web Resource Discovery System" [4].

We use a shopping cart system to process sales, which has become the defacto standard for the internet. Next to the description of each item is a quick link to add the item to the shopping cart. The user can then view the shopping cart, remove items from it, and modify the quantities of each item.

When the user decides to check out and finish their shopping experience, they are prompted for their credit card. If the site has seen this person before (identified by their credit card number) they are prompted for a password. If the customer either gets the password incorrect, or the site hasn't seen the customer before, it prompts the user for their name, address, and other details required to make a positive identification. This can be seen in appendix B, Figure 17.

When browsing the site, the customer can click on "Add to favorites" buttons that add the item to their favorites list. Then, at any time they wish, the customer can view a page that lists all of the items on their favorites list. This technique is not new to computers, and has been used in web browsers for years in the form of bookmarks. Lately many web sites have been integrating this feature in an attempt to make the customer feel like they're more in control. The favorites section gives the user a way to easily remember items that they may want to buy later, and gives us data on which items the customer likes which may be used in several personalization techniques described below.

After a customer has placed an order, they can come back at a later time and check on the status of that order. They can also leave comments for the vendor, request that the order be canceled, and read the comments that the vendor has written about the order.

2.2 Vendors

The vendors are our next most important class of user, for they provide the items to sell. They will need to be able to accomplish a series of functions.

The first thing a vendor needs to do to sell their wares on our site is to register and be accepted. Once the vendor is accepted, they will get a username and password. With this username and password, they will be able to access their account management web page. This is the central point of control for that vendor, and lists all of the available actions that the vendor can perform. The vendor will need to provide their own computer and internet access.

Once a vendor has an account, they will need to identify the goods and services that they have for sale. From their account management web page they will have the option to add new items, manage existing items, and remove existing items. To add an item, the vendor will need to fill in a short form that includes the item's name, price, description, and several other important facts. They will also be able to upload one small and one large image of each item. The small image will have a set size of 150 x 100 pixels (This value is configurable by the site administrators), this is the image that will be used inline with the item in searches and in several other places. The larger image may be of any reasonable size and should provide a more detailed view of the item. A unique feature will be the ability to choose a template. This template will determine how the information about the item will be displayed on the web page, and can be selected from a gallery of pre-made formats. When a vendor has the items he wishes to sell entered in, he merely has to sit back and wait for the orders to come in.

One of the most important functions the vendors will have to do is maintain their inventory. They will have to keep accurate counts of the number of available items they have for sale. To do this, there is a simple form listing all of the items, and the inventories for each. This makes it easy to quickly update several different items. Whenever an item is sold, the inventory value for that item is updated automatically.

Occasionally it may be necessary for the vendor to change the personal information about their business. From the vendor account management page they will have the option to change this information. All changes will be shown on the administrator event log, so the administrators can take appropriate action against vendors entering in bogus information.

The vendor needs to be able to see his orders. This is done through an interface on their vendor home page. Some vendors may not expect to do a large amount of business. These vendors may wish to have an email reminder sent to them once a day if they have any new orders so they don't have to check their new orders every day.

2.3 Administrators

The administrators of the system, the people who run the web site, will also need several features to ensure the smooth operation of the site.

They need an event log. This log lists whatever changes may be made to the status of any order, any system problems, whether daily maintenance functions worked correctly (Like mailing vendors about new orders), and other system-specific items. The view for this log can be filtered to only view certain types of events, so different administrators can concentrate on different aspects of the site.

The administrators also need the ability to change the status of any order. From the administrative home page, the administrators can interactively change the status of any order. Care must be taken if financial transaction had already occurred in these orders, for the system will allow an administrator to double pay a vendor, or refund a customer's credit card more than once.

When vendors sign up for the service, they will be placed into a queue until the administrators verify the information. The administrators can then go into the vendor list, select a vendor, and edit any of the details for that vendor, including whether or not the vendor is active. This way they can change anything if it's offensive or incorrect. They likewise have the ability to change any of the information for the items being sold. This information includes any comments entered by customers. They also have a way to temporarily suspend a vendor or an individual item. The administrative interface for editing vendors can be seen in appendix B, Figures 22 and 23.

2.4 User Tracking

HTTP is a stateless protocol, but we need a way of tracking the user. The system we use will need to be flexible enough to be used for a wide variety of tasks, but require little intervention from the user.

3 Design

3.1 General Design Issues

One of the most important decisions in any project is the selection of a development environment, and more specifically a programming language. During this selection, we looked at several technologies. The first we looked at was writing the system in C or C++. This approach would probably have given us the best performance, but at a cost of longer development time. C just isn't suited well for server side web development. We then looked at Active Server Pages (ASP) from Microsoft. The major downside to this approach would have been our reliance on the Windows NT server platform, or a proprietary ASP engine that could run on other platforms. Java Server Pages (JSP) were a very attractive offer, but are still not widely accepted, and would have presented a problem later on when we tried to find a web hosting company to run the web site on. This left us with only two more obvious solutions, PERL and PHP. We decided to go with PERL since it is more universally available. PERL (the Practical Extraction and Report Language) has been proven as a good language to implement web systems in. We choose to run these perl scripts on an Apache web server, since it's the most widely used web server to date [2].

There are a great number of databases that we could have chosen. The "real" RDBMS such as Oracle, SyBase, and Interbase were all out of our price ranges. We had to settle on MySql (3) which is a free implementation of nearly the entire ANSI SQL standard. Compared to the other database engines, it is quite fast, but this speed comes at a price. MySql does not support transactions or data triggers, meaning extra care had to be taken on the site to preserve data integrity. It is due to this that we use a transaction log in addition to storing the values for the most important tables. Even still, it is impossible to assure no data will be out of sync if the database server crashes or looses it's network connection. The credit card processing company also maintains a database of charges on their side that we can reconcile with so, in the worst case scenario, we can always refund the customer's money.

This project followed an incremental and interactive development model. Craig Larman describes it as, "An iterative life-cycle based on successive enlargement and refinement of a system through multiple development cycles of analysis, design, implementation, and testing" [1]. In the first phase we developed the vendor interface, and the underlying structure for it. The second phase consisted of the customer searching and shopping pieces, and the third stage concentrated on the personalization of the web site.

The software itself is written in many small specialized CGI scripts. The alternative was to write one, or a few, large general purpose scripts. We choose this format because it is quicker to write the functionality of each page and it is easier to understand what each of these scripts does. The major downside to this approach is that it is often difficult to find the correct script that performs an action. We tried to use a naming scheme that would alleviate this.

3.2 Database Design

The web system was designed from the perspective of the data, and everything else was written to work around that data. Due to this fact, it is easiest to describe the design of the system in terms of that data, keeping in mind that there are web pages to edit, view, and modify nearly all of these tables, and those pages can be seen in Appendix B. You can see this design in Appendix A, Figure 3. The interactions between the tables are shown in the descriptions of the individual tables below.

3.3 Table Design

Each table in the system will now be described. First we will show the structure of the table, and then describe anything out of the ordinary about that table, and the important links to other tables.

3.3.1 Accounting

(Database table accounting)

Each record in the accounting table represents one type of financial transaction in the system. Most of these records will be from either money coming in from credit card sales, or payments going out to vendors. The other types of transactions include the paying of bills, or the income from outside investors. The acct_type field identifies the type of accounting record that this it. The amount is the dollar amount (USD) that the transaction is. Money coming into the site is always positive, and money being paid out from the site has negative values, this allows a simple summation of the records to get a quick snapshot of the financial status of the site. Accounting records are the revenue or expenditures that have already happened or are expected to happen, when the money is actually transferred, the accounting records become invoiced, and records in that table are created. You can see the vendor's view of these records in appendix B, Figure 19.

There is also an additional interface for the administrators, so they can create new accounting records, remove records that haven't been invoiced yet, or modify records that haven't been invoiced. Along with each accounting record, there can be notes that describe various aspects of each accounting record. These are stored in the account notes table below.

Since most accounting records are based on an order, we strayed from a completely normalized table structure and added an ordernum and orderitem fields that can be used to reference the orderID and orderItem fields from the orderItem table below. If the item has been invoiced, the ID field from the invoice table will be referenced in the invoice field, otherwise this field is set to null.

3.3.2 accountNotes

(Database Table accountNotes)

The account notes table lists notes that get written for the various accounting records. Many of these notes are automatically generated by the system and include useful data about each transaction. Other notes are manually entered by the administrators to describe extraordinary events.

The accountNotes accountID field references the ID field in the accounting table. There can be many accountNotes for each accounting record.

3.3.3 adAccount

(Database table adAccount)

The advertisement system is still in its infancy, but provides a way to dynamically display different ads, and to keep track of how many of those ads were viewed, and when they were viewed. The adAccount table lists each "account" set up to display ads. This allows one source to have more than one ad that can be displayed on the site. The accountName is a friendly name so that the administrators can differentiate between the advertisers.

3.3.4 adLog

(Database table adLog)

The adLog is used to keep track of what ads were shown or clicked, when it happened, and to whom they were shown. The type field lets us record both views and clicks in the same table. The when field records when the ad was shown, and the ip field records the ip address of the user who viewed or clicked on the item. This table will allow easy billing to advertisers based on the activity of their ads.

The "ad" field references the ID field in an advertisement record.

3.3.5 advertisement

(Database table advertisement)

Each record in the advertisement table lists one possible advertisement that can be shown on the web site. The name field represents a friendly name of the ad that can be displayed to the administrators, or printed on ad reports. The adType describes what type of advertisement the record represents. An h_banner is a horizontal banner that can be displayed across the top or bottom of the screen, v_banner is a vertical banner that is suitable for display on either side of the screen, text_small is a small text-based advertisement (typically under 20 words), and text_large is a large advertisement, perhaps several paragraphs long. The weight parameter determines how often the advertisement is displayed, where the probability of the ad being displayed is equal to the weight over the sum of all the weights of all the advertisements.

The account field links to the adAccount ID field, and must be a valid entry in the adAccount table.

3.3.6 allowedShipping

(Database table allowedShipping)

Each item has a list of ways it may be shipped to the customer. This list is set up when the item is first entered into the system, and can be edited through the vendor interface (Appendix B Figure 22).

This table associates the ID field in the items table with the ID field in the shipping table, with one record for each shipping method allowed for the item. There should not be any duplicate records in this table.

3.3.7 cart

(Database Table cart)

The cart table represents a single shopping cart for a user (Appendix B, Figure 6). The "ip" field is the ip address the user is from, and the when field represents when the shopping cart or the contents of the cart was last edited. Any cart that hasn't been edited in a certain time period should be considered inactive and can be deleted. The length of the persistence of the cart is a business decision and is configurable depending on what the administrators decide.

The customerID is the standard user ID from the user tracking system, and can be related to the ID field in the users table.

3.3.8 cartItems

(Database Table cartItems)

The cartItems table lists the contents of a shopping cart. When a record in the cart table is deleted, all of the cartItems that reference that record should also be deleted. The qty field lists how many of the item is in the user's shopping cart.

The cartID field corresponds to the ID field in the cart table, and there should never be a cart item without a corresponding cart record. The itemID field lists the ID of the item in the cart from the items table.

3.3.9 categories

(Database Table categories)

The categories that items may be in are stored here. Some examples are "Dolls", "Arts and Crafts", and "Clothing". The description field is the displayable name of the category. The score field is able to be set by the administrators, and is a measure of how visible they wish the category to be to people that have not indicated a preference to individual categories. The five highest scored items will be displayed on those user's quick category listing.

3.3.10 catItem

(Database Table catItem)

An item can be in on or more categories, and the catItem table records this. The catItem table is much like the allowedShipping table, except it lists the categories the item is in instead of the methods of shipping allowed for the item. The vendor can edit the categories the items occur in through the vendor interface (Appendix B, Figure 21).

The catID field represents the ID in the categories table, and the itemID represents the item's ID from the items table.

3.3.11 ccauth

(Database table ccauth)

Every time a request to authorize a credit card is sent out to the credit card processing company, a response is sent back to our system. The table ccauth records these responses, which come back from the verification service. There is a limited set of valid responses, and anything but an accepted message (value = 0) should be considered a failure. The respMsg is a textual reason why the transaction was or was not approved. If the transaction was approved, we will be supplied with an authorization code. In the event of a discrepancy between the credit card processing system and our system, we must be able to provide this code. The amount field is the total amount of the transaction and should be equal to the total of the order, if it is not the administrators should be notified.

There is a single script that gets called whenever a response comes back from the verification company. This script fills this table in, updates the status of the order from preliminary to ordered, and creates accounting records to represent the order. If the vendor is set up to receive emails when new orders are placed, it creates a new email request and puts it into the email queue.

The ordernum references one of our internal orders via the ID field in the orders table. Since the credit card is authorized and processed for each order, and not per order item, there should only be one of these for each order.

3.3.12 counter

(Database table counter)

On many pages there is a message at the bottom that displays how many times that particular page has been viewed. Those counts are stored in the counter table, and are referenced by the url of the page being viewed. The script that displays the message is capable of looking at the environment variables supplied by the apache web server and automatically determine what page is being viewed, increments the count for that page, and then outputs that count.

3.3.13 creditCard

(Database Table creditCard)

The customer credit card and billing information is stored in this table. Records in this table can be periodically deleted for users who haven't been active for a certain time period to reduce the damage done if a break in ever occurs. The information in this table is entered during the checkout process (Appendix B, Figure 3).

The customerID field references the ID field in the customer table.

3.3.14 customer

(Database Table customer)

Information about the user is stored in the customer table. The address, state, city, and zip fields all correspond to the user's shipping address. This information is entered during the check out process (Appendix B, Figure 4). The password is used later on when the user wishes to access their account to edit account details, or view order status. This password is stored in an encrypted form. The allowToBuy field allows administrators to mark accounts that cannot purchase items, perhaps due to fraud attempts. If the ccInfo field is set to true, this means that there is at least one record in the creditCard table that represents the user's credit card and billing information. The spend field stores the total amount the user has spent, and can be used as a way to quickly determine your most valuable customers.

3.3.15 email

(Database Table email)

Many different types of emails are sent by the system. These include such things as order notifications for vendors, shipment notifications for customers, and abnormal errors to the administrators. It would be rather inefficient to actually create the email and send it from whatever CGI script wishes to send it off, so an email queue has been created. When a script wishes to send an email, it fills in the appropriate fields in the email table and forgets about it. On a schedule a program runs and scans through the email table looking for new records. When it encounters one it builds the email and attempts to send it out.

The userID field can have several different meanings depending on the userType field. If the userType is set to 'Customer', it corresponds to a customerID, if it is set to 'Vendor' then it corresponds to a vendorID, and if it is set to 'Admin' it corresponds to an administrative user ID. The email program can then look up the email address of the user from either the customer, vendor, or admin tables. The emailType field lets the email queue know what type of email is requested. Some examples of this are 'ORDERS' which is an email to let the user know the order has been shipped, and 'ORDERE' which lets the user (usually the vendor) know an order has been entered. The three email data fields represent parameters for the type of email to be sent. These parameters are different for each email. For the two examples given above they would include the order number and possibly the order item. The emailSent field lets us know if an email has been successfully sent, and the emailDate tells us when that email was sent.

3.3.16 event

(Database Table event)

During the course of operations, many events will be generated. These events can be warnings, errors, or orders abnormally changing status. Administrators can look at these warnings based on the messageType which corresponds to these different types of events. Errors are generally fatal errors in the scripts execution, things such as invalid data in a table found or running out of memory. Warnings are events that happened, and from which the script was able to recover. An example of this is if the customer enters in a name too long for the database, and the script had to truncate it.

If the event is about an order, the order ID is in the reference field. If it was a fatal error, the exit code is in the reference field.

3.3.17 history

(Database Table history)

The history of every order is recorded to reconstruct the chain of events that an order went through in the case of a problem. The history table stores this information. 'Action' is a textual description of the action, and the when field is the date and time the action happened. The only type of event that is not recorded in this table is when an order's status is changed.

The order ID and orderItem both correspond to entries in the orderItem table, and can be related to that table. There can be many events for any single order item.

3.3.18 images

(Database Table images)

Each item has two images associated with it, a full size image and a thumbnail image. More than one item can point to the same image. The images table lists all of the images that the vendor has uploaded to the system's web server. The path field is a relative path from the root web directory to where the image can be found. The 'vendor' field lists the vendor ID of the vendor who uploaded the image. The name is a simple name by which the vendor to remember the image. The lastMod field tells when the image was last modified (either uploaded, or renamed).

When a vendor wishes to upload a new image, he seletcs the name and local file location in an HTML form. When one hits the submit button, the file is sent, and the file is saved in a file with a specially formatted name. Currently these images are configured to go into the /item_images directory of the web server, but this is configurable. After that, the vendor can select that image as either the thumbnail or full-sized image for any item. If they select it as the thumbnail, it is scaled to 150x100 when displayed.

3.3.19 invoice

(Database Table invoice)

Any time money comes in or goes out of the system, an invoice record is created. The datePaid field lists when the money was actually transferred. The note field lists any special notes about the payment which may include check or account numbers. Certain types of accounting records will be grouped together in batches. An example of this is when a check is cut for all of the transactions a vendor did in the previous month. Only one check is cut, but many invoice records may be needed all with the same invBatch number. These invoice records are either created through an interface to the administrators, or automatically by an invoicing program that runs once a month.

The accountingID is a reference to the accounting table's ID field which contains information about the amount of the transaction, when it was recorded, and other useful information.

3.3.20 itemComment

(Database Table itemComment)

From then on customers or administrators can attach comments to items. Whenever a customer looks at the detail of that item, the comments for that item are displayed. The itemComment table holds these comments. The administrators have a method to edit or delete comments.

The itemID references the ID field in the items table, and there can be several comments for each item.

3.3.21 itemPref

(Database table itempref)

The itemPref table represents how much a particular customer likes an item. The lookTime field corresponds to how many seconds the user has looked at the item in total, the lookNum represents how many times the user has looked at the details of the item. The buy field shows how many of the item the customer has bought. The actionLike value is calculated from these fields, and represents the implicit vote that the user gives to the item. The collabLike value is the collaborative filtering algorithm's prediction on how much the user will like the item. This field should only be referenced if there is no actionLike value. The lastLook field is used to calculate the lookTime for the user.

Since the web server only gets requests for pages, and does not record when the user stops looking at the page, we assume the time that the user looks at a page to be from when the first views the page until they look at a different page. Every page of the system has a special script embedded in it that updates any items that the user was looking at.

The customer field represents a record in the users table referenced by the ID field. The item field links to the ID field in the items table. Each customer / item combination in the itemPref table should be unique.

3.3.22 items