Rick Curtis

E-commerce Database Design - Part I

This is the first installment in an explanation of Database Design for Ultradev E-commerce application developers. It's based on my database experience (and all the mistakes I made along the way) with building E-commerce apps in Drumbeat 2000. I hope it's helpful

Starting Your Database Design

The first thing in building any E-commerce app is creating the underlying database to support it. As much as possible, you should try to build your application code and business logic into your database rather than into the client Web page. This will make your application much more portable and will allow your solution to scale upwards from a desktop database like MS Access to enterprise-level databases like SQL Server 7 and Oracle 8. 

Basic Design Principles

Most E-commerce applications use a similar design with 4 basic tables. There may be other ancillary tables to support things like shipping, taxes, categories for searching, etc. I am going to focus on these four core tables.

• Customers Table - holds customer information like address, shipping address, billing address, etc.
• Products Table - holds product information like product name, description, size, color, unit price, etc.
• Orders Table - holds information on when an order was placed including Customer ID, date of order, order shipping date, etc.
• Order Details Table - holds information on each product ordered on one order (since typically you can purchase multiple items on the same order) including the product ordered, quantity, unit price, any discounts, etc.

Before you start creating your database tables you should spend a few minutes with paper and pencil to design each table. Yeah, I know, just jump into Access and starting up table designer. I've done it plenty of times, and later regretted that I didn't think first. In the end I spent much more time repairing and renaming databases that I made too quickly than I would have spent on properly designing them in the first place. 

Naming Conventions

Again, before you get deep into table design it is important to have a good system for naming tables and fields in tables. There are certain names that are reserved for your database things like DATE, NAME, FUNCTION, and others may be reserved. Check your database documentation first to make sure your are not building a database and code around names you can't use. It is best to avoid using spaces in table and field names. Some databases won't handle spaces and others require extra brackets around fields with spaces just adding to the reasons why something won't work. Either use underscore or capitalize on merged words (e.g. first_name or FirstName). 

Sample Database Diagram - SQL Server 7.0

Here is a sample E-commerce application database structure. For a relational database to work properly you should have a field in each database that uniquely identifies that row in your database table. This field is called the Primary Key Field for that table. In SQL 7 and other high-end relational databases, each table is required to have a Unique Row Identifier or Primary Key in order to interact with other tables.  Access does not require this and will link tables without strictly structured relationships. The table below illustrates the relationships between tables. Your particular application may not require all of these tables or all of these fields. 

Click for a full-sized image

Now that we have the big picture, let's look at the individual components of the database and how they relate to each other.

Products Table

Define the Attributes of your Products

In order to order an item it must reside in your database and exist as a unique item with appropriate name, price, and other attributes like size, color, and weight. You'll need to identify where the items from your store are coming from. Is it all your own inventory or are you carrying goods from multiple vendors. If it is your own inventory, you can set up your own Product ID codes using SKU's or your own ID's. Each item will have it's own unique Product ID value. If you are selling from multiple sources it is more complicated. Some vendors may not have a Product ID, others may have an ID code but what if two vendors use the same ID code? So for proper database development you need to set up a Primary Key field for the Products table that will be a unique record for each product (row) in your table. If you have a unique SKU or other unique Product ID coding system you can use that otherwise set up the ProductID field as an Identity Column in SQL 7 typically with an increment value of 1 (Autonumber in Access 2000). Then each time you add a new Product the Autonumber will increment by one for a new unique ProductID field.

In some cases your Products will be unique by themselves. If you are running a bookstore like the infamous Drumbeat 2000 sample E-commerce app it is very simple. Each book is a single product with an ISBN number, book title, author, price, weight, etc. So creating your Product table and populating it with data is easy. You need to simply make a list of all the attributes of your products that are important either for your consumers for purchasing or for you for inventory or other administrative purposes. 

Before you get too busy adding records, think ahead. Will you have products with different sizes or colors. Will they be different prices based on size? (Think about clothing like XXL sizes could it be a different price than other sizes?) If so you may need to create different rows in  for similar products with different attributes (like size) or you may need to create a ProductDetails Table (more on that later). Whatever you do in your Products Table will have a distinct impact on the design of your Orders and OrderDetails tables.

Dealing with Size & Color Attributes

Option 1 - "Quick & Dirty" - Include all options in a single database field. I used this option in a quick outdoor equipment store application that I developed with products from many suppliers. Part of this design was simply to save me work from having to create multiple entries for each product or going with a ProductDetails table. Under this design I had an AvailableSizes field and an AvailableColors field in the products table. Here is a sample:

Now, using this approach meant that on my Web page I could display a data row on a product like the Alpine Summit Backpack and customers could see the sizes and colors in a regular text box on the Web page like so:

To place the order the Customer has to type in the Size and Color she wants. The disadvantage to this approach is that you are leaving it to the customer to type in the correct values. If she messes up and types XS for the Alpine Summit size (which is not an option or types FF for some strange reason) then you will have a hell of a time figuring out what to do with the order. That is why it is a Quick & Dirty approach because it removes the data validation function from your database. A much better approach is to limit the choices the customer can make to only those that actually exit. This constraint means that the data entered is always valid. Something that is essential in a big E-commerce app.

In the next installment we will look at options for doing this.