Chapter 7. Creating Electronic Storefronts and Implementing Shopping Carts

When big business started to think about making money on the Web, the first thing they thought of (naturally) was selling things. Companies like Amazon.com reinforced this impression, and the first indicators people looked at for success were based on sales revenue.

These days, companies are beginning to realize that selling products is only one way to make money on the Internet, but it's still a very important way. Basically, there are two ways to make money by selling products. The first, and most obvious, is to sell a lot of products and make a profit on them. The second, and less obvious, is to use the products as a draw—even, in some cases, pricing items below cost—to get customers to your site to make money via advertising and other avenues.

In either case, your goals are the same: the customer should be able to find what they need (or want) quickly and easily and should be able to part with their money as painlessly as possible. Presenting your products is fairly straightforward. The tricky part is keeping track of the items customers want. Ideally, they would be able to select an item as they are looking at it, then choose something else, and so on until they were ready to check out. This type of system is known as a shopping cart, and there is no shortage of programs out there that will do it for you. In order to understand the issues involved in implementing one of these systems, we're going to build one of our own.

Our store is going to have the following capabilities:

• Products will be categorized and may fit into more than one category.

• Searches will be possible by category, product name, product description, or price level.

• Orders will be taken online, although we will be using manual credit card processing instead of automatic.

In order to do all this, see the basic model in Figure 7.1.

Each category may consist of one or more products, each of which must belong to one or more categories. Each product may be a part of one or more orders, each of which may contain one or more products. These are all many-to-many relationships, and we'll need to create some tables specifically to manage them. Create the tables shown in Listing 7.1.

Code Listing 7.1. Bazaar table create scripts

create table baz_categories (
    cat_id      integer,
    cat_name    char(100)
)

create table baz_products(
    prod_id     integer,
    prod_name   char(100),
    prod_desc   char(255),
    prod_image  char(100),
    prod_price  numeric(18,2)
)

create table baz_product_categories (
    prod_id     integer,
    cat_id      integer
)

create table baz_orders (
    order_id    integer,
    first_name  char(100),
    last_name   char(100),
    address1    char(255),
    address2   char(255),
    city   char(255),
    state     char(100),
    postalcode  char(50),
    country   char(100),
    phone     char(20),
    shipping   numeric(18,2),
    total     numeric(18,2)
    cc_type   char(50),
    cc_number   char(20),
    cc_exp    char(5),
    cc_nameoncard char(100),
    status   char(25)
)

create table baz_order_items (
    order_id   integer,
    prod_id    integer,
    quantity   integer
)

First of all, we're starting all of our table names with baz_ to indicate that they're part of the Bazaar, the same way we started our tables with auction_ in the previous chapter. This way we don't have any conflicts between our product categories and any other categories, such as our news.

Please note that even though we only have three entities (categories, products, and orders) we actually need five tables to tie them all together. This gives us the flexibility to create multiple associations between items; a product can belong to more than one category, and an order can have any number of items. This is one of the advantages of relational databases. If we tried to do this all in one table, we'd have to anticipate a maximum number of items per order, and so on.

After you've gotten the tables created, go into your database and create some categories and products. Try to create some that will fit into more than one category. Some suggestions are in Table 7.1:

You might also consider giving them different price ranges so we can search on them later.

Not only do we have to create the categories and the products, we have to create the records that tie them all together (see Figure 7.2). For instance, the Titius IV Rocket Jet Pack will actually have three records in the baz_product_categories table—one for each category it belongs to.

Ultimately, we can do all of this maintenance over the Web, and we'll take a look at that in our next chapter. For now, though, we'll concentrate on the task at hand: building an online storefront.