Extreme Ultradev - E-Commmerce Database Design Part II

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