Outline
COMP344 E-Commerce Technologies Relational database review A typical schema overview
E-Commerce Database Schemas Les Bell les.bell@mq.edu.au E6A 256
1
User tables Store tables Product catalog Discounts Shipping Orders Other tables
2
Relations Are Tables
Database Schemas for e-Commerce
Most enterprises cant afford to develop an ecommerce merchant server application from scratch Even if they did, creating a database schema with tables and columns reflecting their product line would be inflexible and difficult to maintain So, merchant servers need to have quite complex database schemas
3 4
Foreign key
Primary key
WebSphere Commerce
The examples which follow are based on IBM WebSphere Commerce, one of the most successful merchant server products. Combination of
IBM DB2 database IBM HTTP Server (Apache, really) WebSphere Java Application Server Lots of additional Java code, templates, tools and examples
Escape Clause
The examples that follow are from an extremely sophisticated merchant server product. You do not have to replicate these in your assignment!
But hopefully, they will stimulate your thinking and show you what a real-world server looks like.
Expensive Express Edition for small business is $US4,600
5
There are over 600 tables in WebSphere Commerce 6.x!
6
Schema Overview
Keeping Track of Shoppers
Do you really want a customer to enter shipping address information every time? Do customers always ship to the same address? What about good customers can or should you offer them a better deal?
Keeping Track of Shoppers
Allow customers to register so we need a table for their user ID, password, cookie, etc.
WebSphere Commerce calls this table SHOPPER
A row for every visitor, even anonymous shoppers, so small
Shoppers and Users Table
Every user of the system shopper or administrator has an entry in the SHOPPER table
Have a separate table for addresses each shopper can have multiple addresses for personal, business, gifts, etc.
SHADDR
Optionally, allocate each shopper to a shopper group
SHOPGRP
10
Multiple Stores on One Server
Do you want to operate your own server, or have your store hosted? You have mastered selling t-shirts online, but now have sourced macrobiotic wholefoods. Do you use the same store, brand and image?
Multiple Stores
WebSphere Commerce can host multiple stores (merchants) on one server (mall) Each merchant has an entry in a MERCHANT table There can be a mall home page, which lists stores by category
Some stores could be in multiple categories (STRCGRY table)
Other tables store tax information and map shoppers to stores
Tax is complicated e.g. no GST on exports, and the US has state sales taxes, etc. Shoppers only need to register once, for all stores
11
12
Mall and Merchants Tables
Think of the server as a mall it can support multiple stores or merchants. Mail order companies often present multiple different brands in different categories, especially at different price points.
The Store Catalogue
How do people find products in your store? Do computer mouses go under input devices or accessories? Or both? How do you display product information?
13
14
The Store Catalog
All content is presented via template pages (Java Server Pages - .jsp in the case of WC)
Category templates
Typically display either subcategories or a list of products Can display special offers, featured products, etc.
The Store Catalog (cont)
In order for products to be easy to find, they may appear in multiple categories So we need a one-many relationship between products and categories
CGPRREL table
Product templates
Typically display one product, with illustration(s), description, form fields for attributes (colour, size, etc.) and the all-important Add to Cart button
15
Similarly, if categories are a strict tree, customers will be frustrated if they take the wrong branch, so we need a many-many relationship
CGRYREL table
16
Product Information
Key product information goes in a single table
PRODUCT
Product Pricing
Products can have multiple prices
E.g. retail vs trade, club membership, etc. This is independent of any discounts that may be applied for quantity, order amount, etc.
Attributes
Size, colour, voltage, material,
But attributes go into separate tables
One for attribute names (PRODSTATR) One for attribute values (PRODATR) A product with specific attribute values is called an item
17
WebSphere Commerce has Shopper Groups
Product has a price per shopper group (PRODPRCS) Shoppers get different page views (PRODSGP, CATESGP)
18
Categories and Products Tables
These tables form the product catalogue of the site.
Discounts
You may want to offer a discount on
Product quantity Total order value Promotional code
WebSphere Commerce has code to calculate discounts in various ways
Based on product, shopper group
19
20
Discounts Tables
Shipping
Shipping costs vary according to product size and/or weight, as well as shipping method WCS supports shipping modes and shipping codes
21
22
Shipping Tables
Taking Orders
Almost forgot this one! Its not easy:
Suborders shipped to different addresses, with different shipping modes Various payment methods Wish lists, etc.
23
24
Orders
At the end of the process, the orders are in the ORDERS table SHOPPINGS is a table used for wishlists, repeat orders, etc.
Orders Tables
25
26
Other Stuff
We still havent dealt with
Multiple currency support Payment processing (credit cards, etc.) Page caching Advanced product recommendation functions Analytics Mapping commands to stores Multiple language support And a lot more!
Other Database Table Groups
Later versions of WebSphere Commerce add even more groups of tables, e.g. for B2B trading
See http://publib.boulder.ibm.com/infocenter/wchelp/v6r0m0/nav/11_10_0
27 28
Summary
Real-world merchant server database schemas are complex
WCS had over 600 tables, last time I looked
Expect to spend quite a bit of time learning about any product you work with There are lots of complications in real-world e-commerce expect to have to refine any database schema you design from scratch
29
