Online store app RESTful routes and general schema

Published February 24, 2007 in REST, Schema, Store

Quite a long while ago I worked on online stores and wanted to develop a generic Rails application like osCommerce. Work lead me to JavaScript and away from Rails so the store project died. Two good things came from my store project: a set of RESTful URLs for a store and a database schema. Here they are.

RESTful routes

Here are the raw URLs that show which action are in which controllers of a Rails app. Surprisingly a good and big influence on this design was the "edit this item" button in the cart. That is what taught me that the product page is really a cart item creation page at it's essence.

GET http://store.com/

  • store splash page entrance
  • list of departments

GET http://store.com/departments/23

  • department 23 page
  • list of products in the department
  • probably a good idea to include the department name in the URL as http://store.com/departments/23-t-shirts

GET http://store.com/cart_items/new?product=31

  • what we think of as the product 31 page
  • shows the form to create a new cart_item based on product 31 with
  • options and quantity
  • has the "add to cart" button
  • probably a good idea to disguise this URL as http://store.com/product/31-Led-Zepplin-tour-t-shirt

POST http://store.com/cart_items

  • create a new cart item based on form information including product id
  • redirects to cart

GET http://store.com/cart_items/612;edit

  • show the form to edit a cart item. If this cart item is for product 31
  • then we will see this as the product 31 page again but the button will
  • say "update" instead of "add to cart".

PUT http://store.com/cart_items

  • updates a cart item based on form information including cart_item id
  • redirects to cart

DELETE http://store.com/cart_items/612

  • delete link shown on cart view page
  • removes an item from the cart
  • wants html: redirects to cart
  • wants js: sends javascript to update the cart view

GET http://store.com/cart_items

  • show the shopping cart
  • has the "checkout" button

GET http://store.com/purchases/new

  • form to checkout (shipping, credit card info)
  • has the "finalize" button

POST http://store.com/purchases

  • finalize the purchase
  • redirects to "thanks for purchase page"

GET http://store.com/purchases/55

  • the "thanks for purchase page" with a receipt.

GET http://store.com/purchases

  • customer order history with shipping status etc

GET http://store.com/customers/new

  • form to create an account

POST http://store.com/customers

  • create a new customer account
  • starts a new session like logging in would
  • redirect to "thanks for signing up page"

GET http://store.com/customers/77

  • show customer his details

GET http://store.com/customers/77;edit

  • edit customer details

PUT http://store.com/customers/77

  • update customer details
  • redirect to show customer details

GET http://store.com/sessions/new

  • customer login page (sessions are sort of singletons per customer)

DELETE http://store.com/sessions

  • customer logout
  • does not actually delete the session as another login can restore it.
  • redirect to "thank you for shopping. Please visit again" page or back to the page where
  • the logout was initiated if allowed there in logged out state

The Schema

This schema is very flexible and difficult to understand at first. I think it is worth the effort to study and then can be pared down if appropriate. This schema comes from before Rails migrations and so is written in raw SQL for MySQL.

# Departments are intended to act as tree.
# For a particular Department the subdepartments are
# intended to act as and ordered list.
# e.g. T-shirts
# Could make the departments a general directed graph
# instead of a tree graph but then it is difficult
# to make a bread crumb trail on the web pages.
CREATE TABLE departments (
  id bigint(20) unsigned NOT NULL auto_increment,
  parent_id int(10) unsigned default NULL,
  name varchar(255) NOT NULL default '',
  description text NOT NULL,
  position int(10) unsigned NOT NULL default '0',
  UNIQUE KEY id (id)
);
# Need to seed the database with the root department
INSERT INTO departments VALUES (1, NULL, 'Root', 'Top level department.', 1);

# A Department has many products.
# A Product can be placed into more than one department 
# and so a product has many departments.
# The products in a department act as an ordered list.
CREATE TABLE placements (
  association_id bigint(20) unsigned NOT NULL auto_increment,
  department_id int(10) unsigned NOT NULL default '0',
  product_id int(10) unsigned NOT NULL default '0',
  position int(10) unsigned NOT NULL default '0',
  UNIQUE KEY association_id (association_id)
);

# A product is not what the customer buys.
# A product is a grouping of closely related items
# that a customer can buy: variations.
# e.g. Led Zepplin Tour T-shirt
CREATE TABLE products (
  id bigint(20) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL default '',
  description text NOT NULL,
  quantity_units varchar(255) NOT NULL default '',
  quantity_precision decimal(30,15) NOT NULL default '1.000000000000000',
  quantity_default decimal(30,15) NOT NULL default '1.000000000000000',
  quantity_is_adjustable int(1) NOT NULL default '1',
  default_variation_id int(10) unsigned default NULL,
  UNIQUE KEY id (id)
);

# A product has many variations.
# A variation has many products.
# The variations in a product act as an ordered list.
CREATE TABLE product_variations (
  association_id bigint(20) unsigned NOT NULL auto_increment,
  product_id int(10) unsigned default NULL,
  variation_id int(10) unsigned default NULL,
  position int(10) unsigned NOT NULL default '0',
  UNIQUE KEY association_id (association_id)
);

# Variations are atomic items that can be bought by a customer
# and inventoried by the warehouse.
# A variations has a price.
# A customer never views a variation page. The customer
# chooses a variation on a particular product page
# e.g. black, extra-large Led Zepplin Tour T-shirt
CREATE TABLE variations (
  id bigint(20) unsigned NOT NULL auto_increment,
  sku varchar(255) NOT NULL default '',
  name varchar(255) NOT NULL default '',
  description text NOT NULL,
  price decimal(10,2) NOT NULL default '0.00',
  UNIQUE KEY id (id),
  UNIQUE KEY sku (sku)
);

# Options are what help guide the user from a particular
# product down to the variation they want to buy.
# Imagine each option as a select element on the product page
# e.g. colour
CREATE TABLE options (
  id bigint(20) unsigned NOT NULL auto_increment,
  name varchar(255) NOT NULL default '',
  description text NOT NULL DEFAULT '',
  UNIQUE KEY id (id),
);

# A choice is a value of an option.
# On the page, choices are the option elements inside an options select element
# e.g. black
CREATE TABLE choices (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
 option_id INT UNSIGNED NOT NULL,
 name text NOT NULL,
 position INT UNSIGNED NOT NULL
);

# Connect options with products
# The options in a product are ordered as a list for
# control of how the HTML page is rendered and potentially
# to lead the user through the steps of the various options
# which ultimately specifies the variation
CREATE TABLE product_options (
 association_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
 option_id INT UNSIGNED NOT NULL, 
 product_id INT UNSIGNED NOT NULL, 
 position INT UNSIGNED NOT NULL
);

#  Connect choices with variations
CREATE TABLE variation_choices (
 association_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
 choice_id INT UNSIGNED NOT NULL, 
 variation_id INT UNSIGNED NOT NULL
);

# Parts are what turn a purchase from just one variation 
# into a bundle of variations.
# On a computer site when you specify your basic computer
# you have to choose a hard drive and printer. 
# These are parts because they can be inventoried separately
# and the total price of a purchase is the price of the main
# variation and the price of the additional parts.
# Parts are select elements on the page (similar to options.)
# Need to consider carefully how quantity will be handled. 
# Simplest is just quantities of 1. Could allow the user
# to select quanity for each part. It gets complicated but 
# is still very possible.
CREATE TABLE parts (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE, 
 name text NOT NULL,
 description text NOT NULL DEFAULT '', 
 default_variation_id INT UNSIGNED NULL DEFAULT NULL, 
 null_option INT(1) NOT NULL DEFAULT '0'
);

# A part select element in the page has several variations as
# its options.
CREATE TABLE part_variations (
 association_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
 part_id INT UNSIGNED NOT NULL, 
 variation_id INT UNSIGNED NOT NULL, 
 join_position INT UNSIGNED NOT NULL
);

# A particular product has many parts associated with it
# This is how you know which parts to display on a particular
# product's page.
CREATE TABLE product_parts (
 association_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
 product_id INT UNSIGNED NOT NULL,
 part_id INT UNSIGNED NOT NULL, 
 position INT UNSIGNED NOT NULL
);

# on a particular product page you may want to say 
# "May we also suggest these products..." which links
# to some cross sell products.
CREATE TABLE cross_sells (
  association_id bigint(20) unsigned NOT NULL auto_increment,
  cross_seller_product_id int(10) unsigned NOT NULL default '0',
  cross_sell_product_id int(10) unsigned NOT NULL default '0',
  position int(10) unsigned NOT NULL default '0',
  UNIQUE KEY association_id (association_id)
);

# each user has a cart
CREATE TABLE carts (
  id bigint(20) unsigned NOT NULL auto_increment,
  session_id bigint(20) unsigned NOT NULL default '0',
  UNIQUE KEY id (id)
);

# each cart has many items (aka variations) in it
# Need to keep track of the product also because
# a variation may be in several products for some reason
# The product is needed if there is a "edit this item" 
# button in the cart.
CREATE TABLE cart_variations (
  id bigint(20) unsigned NOT NULL auto_increment,
  variation_id int(10) unsigned NOT NULL default '0',
  product_id int(10) unsigned NOT NULL default '0',
  cart_id int(10) unsigned NOT NULL default '0',
  quantity decimal(30,15) default NULL,
  UNIQUE KEY id (id)
);

# Keep track of which parts were chosen with each variation
# to make the purchase a bundle
# Need to consider quanitity here if user was able to select
# a quanitity for each part.
CREATE TABLE cart_variation_parts (
  id bigint(20) unsigned NOT NULL auto_increment,
  cart_variation_id int(10) unsigned NOT NULL default '0',
  variation_id int(10) unsigned default NULL,
  part_id int(10) unsigned NOT NULL default '0',
  UNIQUE KEY id (id)
);

Comments

Have something to write? Comment on this article.