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. 

Table Primary Key Related Table & It's Primary Key Foreign Key to Relate Tables
Suppliers SupplierID Products - Product ID SupplierID
Products ProductID Category - CategoryID CategoryID
Orders OrderID OrderDetails - OrderDetailsID OrderID
Customers CustomerID Orders - OrderID CustomerID
Shippers ShipperID Orders - OrderID ShipperID
Payment PaymentID Orders - OrderID Payment ID

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:

ProductID ProductName AvailableSizes AvailableColors
1 Alpine Summit Backpack S, M, L Mango, Cobalt Blue, Black
2 ErgoNom Day Pack XS, S, M, L, XL Mango, Cobalt Blue, Black
3 Wind River Expedition Pack S, M, L Cobalt Blue, Black

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:

Product   Available Sizes    Available Colors
     

 Please enter the Quantity, Size and Color

   

Quantity        

Size      

Color  

 

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.


 

Copyright 2000 All rights reserved Rick Curtis, Princeton, NJ, USA
Macromedia and UltraDev are trademarks of the Macromedia Corporation.
- Page 2 -

Rick Curtis

E-commerce Database Design - Part II

This is the second 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

Defining the Product Table

Here are the details to two versions of Product Tables, one for Access 2000/97 and one for SQL Server 7. Not all of these fields are necessary in one database. For example, with Size and color you can see the "Quick & Dirty" approach using AvailableSizes and AvaliableColors or the SizeID and ColorID approach where the information is kept in a related table. Scan through it and remove the ones you don't need and add others that are appropriate to cover the attributes of your product line.

Field Name SQL 7 Data Type Access 2000/97 Data Type Notes
ProductID (Primary Key) [int] Integer This is set as an Identity field in SQL 7 with an increment of 1 and as an Autonumber field in Access. Can be used to link to ProductDetails Table.
SKU [nvarchar](50) Text(50) SKU number from you or the Supplier
SupplierProductID [nvarchar](50) Text(50) The Vendor's Product ID (could be SKU or their own system). (Could be a duplication of SKU above.)
ProductName [nvarchar](60) Text(60) Product Name
ProductDescription [nvarchar](255) Text(255) Product Description - could be an ntext field in SQL 7 or memo field in Access if there was lots of text.
SupplierID [int] Integer Supplier ID. This is Foreign Key to Suppliers Table. Allows you to display items only from one or selected suppliers.
CategoryID [int] Integer Category ID. This is the Foreign Key to the Category Table. Allows you to display items only from one or selected categories.
QuantityPerUnit [int] Integer Quantity that items are shipped per unit from supplier. E.g. 6/case. Mostly for inventory and ordering purposes. Can be used in arithmetic expressions.
UnitSize [nvarchar](20) Text(20) Unit Size - goes with QuantityPerUnit. This is case, each, dozen, etc.
UnitPrice [money]   Price per single item. Could be retail price or wholesale price.
MSRP [money] Currency Manufacturer's Suggested Retail Price. This may be different than the Unit Price and helps when you are showing discounts off MSRP.
AvailableSize [nvarchar](50) Text(50) Available Sizes
AvailableColors [nvarchar](100) Text(100)  
SizeID [int] Integer SizeID - used to link to separate Sizes Table
ColorID [int] Text(50) SizeID - used to link to separate Colors Table
Discount [decimal]   Discount percentage per item. If you have multiple discount levels (for example with different membership levels) you might have Discount1, Discount2, Discount 3.
UnitWeight [real]   Item weight for shipping calculations
UnitsInStock [smallint] Integer Units currently in stock - used in inventory
UnitsOnOrder [smallint] Integer Units on order - this should be incremented by the shopping cart when orders are placed.
ReorderLevel [smallint] Integer Reorder Level - When to Reorder products. Drumbeat E-commerce used UnitsInStock - UnitsonOrder = X
If X is > ReorderLevel then "Item is in Stock"
If X is <= ReorderLevel then "Item is Out of Stock" This is helpful to display to customers and useful for inventory purposes
ProductAvailable [bit] Yes/No Used to turn a product on or off in the store. Note: I also have a SupplierAvailable Yes/No field in my Suppliers Table. My AvailableProducts query looks at both. So I can turn on or off ALL items from one Supplier by changing the bit value in the Suppliers Table or individual Products by using the bit value in the Products Table.
DiscountAvailable [bit] Yes/No Turns Discount on or off
CurrentOrder [bit] Yes/No Similar to ProductAvailable and duplicates the function. Allows an item to be available or not.
Picture [nvarchar](50) Text(50) Link to an Image file of the product or a URL reference to the image
Ranking [int] Integer a product ranking used for displaying item specials or showing certain items as higher in a sort
Note [varchar](255) Text(255) Notes on the product - like only available between December and January

Okay, now you have your Products Table all sketched out. Let's quickly go through the Suppliers Table.

Defining a Suppliers Table

Here is a basic Suppliers Table. Modify it for your own purposes. The Suppliers Table has a One-to-Many Relationship to the Products Table (one Supplier can have many Products).

Field Name SQL 7 Data Type Access 2000/97 Data Type Notes
SupplierID (Primary Key) [int] Integer This is set as an Identity field in SQL 7 with an increment of 1 and as an Autonumber field in Access. Can be used to link to Product Table.
CompanyName [nvarchar](50) Text(40) Company Name
ContactFName [nvarchar](30), Text(30) Company Contact First Name
ContactLName [nvarchar](50) Text(50) Company Contact Last Name
ContactTitle [nvarchar](30) Text(30)  
Address1 [nvarchar](60) Text(60)  
Address2 [nvarchar](50), Text(50)  
City [nvarchar](15) Text(15)  
State [nvarchar](25) Text(25)  
PostalCode [nvarchar](15) Text(15)  
Country [nvarchar](50) Text(50)  
Phone [nvarchar](25) Text(25)  
Fax [nvarchar](25) Text(25)  
Email [nvarchar](75) Text(75)  
WebSite [nvarchar](100) Text(100)  
PaymentMethods [nvarchar](100) Text(100) Description of how you pay the Supplier (check, Purchase order, credit card, Net 30, etc.). This can be held as text or connected to a separate PaymentTypes Table using a PaymentID in both the Suppliers Table and the PaymentTypes Table.
DiscountType [nvarchar](100) Text(100) Description of Types of Discounts available from the Supplier
DiscountRate     If there is a standard discount percentage, you can set it here and then apply it to All Products from this Supplier.
TypeGoods [nvarchar](255) Text(255) Description of types of goods available from the Supplier. This can be held as text or connected to a separate GoodsCategory Table using a CategoryID in both the Suppliers Table and the GoodsCategory Table.
DiscountAvailable [bit], Yes/No Sets Discount available on or off
CurrentOrder [bit] Yes/No Reorder Level - When to Reorder products. Drumbeat E-commerce used UnitsInStock - UnitsonOrder = X
If X is > ReorderLevel then "Item is in Stock"
If X is <= ReorderLevel then "Item is Out of Stock" This is helpful to display to customers and useful for inventory purposes
CustomerID [varchar](50) Text(50) Your customer ID with the Supplier.
SizeURL [varchar](100) Text(100) A URL to the Supplier Web Page with sizing info on their products (helpful for your customers)
SizeURL [varchar](100) Text(100) A URL to the Supplier Web Page with color info on their products (helpful for your customers)
Logo [nvarchar](75) Text(75) Link to an Image file of the Supplier's Logo or a URL reference to the image
Ranking [int] Integer a product ranking used for displaying Supplier specials or showing certain items as higher in a sort. Like the individual item ranking, here you can set one Supplier to show higher on a list than another (regardless of alphabet)
Note [varchar](255) Text(255) Notes on the Supplier

Now we are ready to move on to the Orders & OrderDetails Tables.

Defining the Orders Table

Here is a sample Orders Table. For each order a new row is created in the table. Since a customer may order multiple items at one time, the actual product information for each order (quantity, size color, ProductID, etc.) are stored in a separate OrderDetails Table. The two Tables are linked by the OrderID (which in most cases would be an Autonumber field in Access or an Identify field in SQL 7). The Orders Table has a One-to-Many Relationship to the OrderDetails Table (one Order can have many OrderDetails)

Field Name SQL 7 Data Type Access 2000/97 Data Type Notes
OrderID - Primary Key [int] Integer This is set as an Identity field in SQL 7 with an increment of 1 and as an Autonumber field in Access. Can be used to link to ProductDetails Table.
CustomerID [nvarchar](50) Text (50) Used as the Foreign Key to your Customers Table. This can be a text datatype or an Integer depending on your preference as long as each CustomerID is unique. In one application I use a University ID number which is unique for each student but which uses leading zeroes so I have to store it as text (same with Social Security Number)
PaymentID [int] Integer This is a Foreign Key to a PaymentTypes Table so that the customer can select payment options from a List Box driven by the PaymentTypes Table (e.g. Check, Credit Card, Purchase Order)
OrderDate [datetime] Date Date the order was placed. Usually set at the Database level Now() in Access and GetDate() in SQL 7. As soon as the record is written the current date from the Server the database is on is written. For international ordering you may want to think about Greenwich Mean Time.
RequiredDate [datetime] Date Date the items are required by the customer.
ShipDate [datetime] Date Date the items where shipped. If you are not shipping all items together then you may want to have a ShippingDate field in the OrderDetails Table so you can track exactly what items shipped on what day.
ShipperID [int] Integer This is the Foreign Key to the Shippers Table that says what shipping company is used. If you use more than one company and do not ship complete then you may want to have a ShipperID in the OrderDetails table so you can reference that one item went on Date X Federal Express and the other items went on Date Y UPS Ground.
Freight [money] Currency Freight Charges. Again it is in this table only if things are shipped complete. If not you would need to track individual shipping charges in the OrderDetails Table.
SalesTax [money] Currency Sales Tax on the entire order
Timestamp [nvarchar](50) Text (50) A time stamp
TransactStatus [nvarchar](50) Text (50) Used by CyberCash for credit card transaction approval
ErrLoc [nvarchar](50) Text (50) Used by CyberCash for credit card transaction approval
ErrMsg [nvarchar](250) Text (250) Used by CyberCash for credit card transaction approval
Fulfilled [bit] Yes/No  
Deleted [bit] Yes/No  
Paid [money] Currency  
PaymentDate [datetime] Date  

Defining the OrderDetails Table

The OrderDetails Table stores the information about each particular product that is being ordered. If the person only orders one item there will be one row added to the Orders Table for the new order and one row added to the OrderDetails Table. If the person orders 25 items there will be one row added to the Orders Table for the new order and twenty-five rows added to the OrderDetails Table.

There is an important distinction that comes up here between Access and SQL Server that is critical to your table design and future expandability. Access does not comply with the ANSI SQL specifications and therefore allows you to have a table that does not have a Unique Row Identifier. SQL Server will not let you do this. In SQL Server you must have a field that uniquely identifies each row. In SQL 7 you need an OrderDetailID as the Primary Key to uniquely identify each row in the table. In addition you have the OrderID field which serves as the Foreign Key to the Orders table. This lets you create your One-to-Many Relationship between the Orders Table and the OrderDetails Table. I would strongly urge you to set up Access the same way. That way if you upsize your Access database to SQL Server, you won't have to add the OrderDetailID later.

Field Name SQL 7 Data Type Access 2000/97 Data Type Notes
OrderDetailID - Primary Key [int] Integer This is set as an Identity field in SQL 7 with an increment of 1 and as an Autonumber field in Access. Can be used to link to ProductDetails Table.
OrderID - Foreign Key to Orders Table [int] Integer This is the Foreign Key to the OrderDetails Table.
ProductID [int] Integer This is the Foreign Key to the Products Table.
Price [money] Integer Price per item
Quantity [smallint] Integer Number of items ordered
Discount [real]   Any discount applied to the individual item
Total [money] Currency This is typically a calculated field based on Price * Quantity * Discount
Size [nvarchar](50) Text (50) Size
Color [nvarchar](50) Text (50) Color
Fulfilled [bit] Yes/No This part of the order has been fulfilled.
BillDate [datetime] Date Date that the bill was issued for the item. Useful if you bill in separate increments based on when you ship.
ShipDate [datetime] Date Date the items where shipped. If you are not shipping all items together then you may want to have a ShippingDate field in the OrderDetails Table so you can track exactly what items shipped on what day.
ShipperID [int] Integer This is the Foreign Key to the Shippers Table that says what shipping company is used. If you use more than one company and do not ship complete then you may want to have a ShipperID in the OrderDetails table so you can reference that one item went on Date X Federal Express and the other items went on Date Y UPS Ground.
Freight [money] Currency Freight Charges. Again it is in this table only if things are shipped complete. If not you would need to track individual shipping charges in the OrderDetails Table.
SalesTax [money] Currency Sales Tax on the entire order

Now that we have our four basic tables defined there are a few more things to think about before starting your Web application. Think a little bit about the types of products you are selling and the types of orders you may get from your customers. If you are selling books like Amazon or other items that don't have any other special attributes you are done. But let's go back to the Size & Color issue.

Let's say that Sue orders 3 T-shirts: one small blue, one medium red, and one large white. How will your E-commerce app handle this? Will she need to make three separate selections in the shopping cart or can she select 3 for the T-shirt quantity and then identify what Size/Color combo she needs for each one? (For simplicities sake we will assume that all three items are the same price but if not it gets even more complicated). Let's go back to your Products Table. If all three shirts have the same ProductID regardless of the color or size then the assumption would be that she enters 3 into the Quantity edit box on the Shopping Cart. How are you going to get the other information from her about correct size/color? Now you know why you want to spend some up front time designing your database properly.

Defining the ProductDetails Table

Well, let's start with the easy approach, simple products and colors with no price variations. The table itself is quite simple.

Field Name SQL 7 Data Type Access 2000/97 Data Type Notes
ProductDetailID - Primary Key [int] Integer This is set as an Identity field in SQL 7 with an increment of 1 and as an Autonumber field in Access. Can be used to link to ProductDetails Table.
ProductID - Foreign Key to Products Table [int] Integer This is the Foreign Key to the Products Table.
Color [nvarchar](50) Text (50) Size
Size [nvarchar](50) Text (50) Size

Here is some sample data. As you can see since we have two attributes, size and color, we have to show all possible combinations for each product in the ProductDetails Table.

ProductDetailID ProductID Color Size
1 136 Red XS
2 136 Black XS
3 136 White XS
4 136 Red S
5 136 Black S
6 136 White S

If we run a query that combines the Products Table and the Product Details Table (more on queries later) we would see something like this.

ProductID Product Name Color Size
136 Alpine Tundra Rain Jacket Red XS
136 Alpine Tundra Rain Jacket Black XS
136 Alpine Tundra Rain Jacket White XS
136 Alpine Tundra Rain Jacket Red S
136 Alpine Tundra Rain Jacket Black S
136 Alpine Tundra Rain Jacket White S

 


 

Copyright 2000 All rights reserved Rick Curtis, Princeton, NJ, USA
Macromedia and UltraDev are trademarks of the Macromedia Corporation.