# cart.dbd - statements to create the shopping cart database tables
#
#  Dave Stoddard - 3/06/2002
#  dgs@accelix.com
#
#  To Do List:
#
#  * add table to validate states and countries for shipment
#  * add table for sales tax
#  * handle shipping charges
#  * handle members
#  * handle discounts and specials
#  * we don't need a session number table if we use $ENV{UNIQUE_ID}
#  * use the item table and its support tables
#  * message text should be controlled via a message table
#
#  Copyright
#  ---------
#  All software, documentation, and data for this application is
#  Copyright(c) 2004, Accelix LLC.  All Rights Reserved.
#
#  Description
#  -----------
#  This file contains statements for defining the database tables used by the
#  shopping cart application.  This file is passed via stdin to the mysql
#  program, which processes the definitions in the file and either issues
#  errors or creates the database and its supporting tables.
#
#  Tables
#  ------
#  system   : defines default characteristics of the application
#  member   : defines members and their attributes
#  item     : defines items for sale
#  stock    : defines items stock codes (out of stock)
#  category : defines application categories and their hierarchies
#  mfgr     : defines manufacturer codes
#  recom    : defines recommendation codes
#  vendor   : defines vendor or drop shipper
#  session  : defines session data for current user (state information)
#  cart     : defines the contents of the shopping cart
#  shipto   : defines shipping info for a customer
#  billto   : defines billing info for a customer
#  orders   : defines customer order
#  orderd   : defines order detail for each item
#  xmit     : defines transmitted drop ship orders
#
#  Notes
#  -----
#  The syntax checker for MySQL is extremely picky.  If you get a syntax
#  error, here are some things to check to locate the error:
#
#  *  Make sure you are using parenthesis instead of braces (like you would
#     use in a C program).  This error is very difficult to find if you
#     are new to MySQL and assume the { and } characters define blocks.
#
#  *  Check the last item in the table to make sure it does NOT have a
#     comma after it.  The syntax checker will not identify the line, instead
#     it will point to the beginning of the table definition.
#
#  *  Check for any UNIQUE statements -- they must be followed by the word
#     INDEX.  This is not clear in the documentation.
#
#  *  Watch out for reserved words.  They are flagged ambiguously.  If you
#     think there is a problem, try renaming the data element or table name.
#
#  *  These statements are designed to work with MySQL 3.23.
#
#  Revision History
#  ----------------
#  1.0 - 06/02/2004 - d. stoddard - initial schema completed.
#
#  $Id$
#
#  $Log$

###
###  The following section is used to create the database and establish it
###  as our reference database for creating our tables.
###

# delete and recreate the database
drop database cartdb;
create database cartdb;

# select the database and verify
use cartdb;
select database();

###
###  The following declarations define system table structures.
###

#
# system : defines system behavior and default characteristics
#

create table system
(
  sysid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  company CHAR(60),			# company name
  address1 CHAR(60),			# street address line 1
  address2 CHAR(60),			# street address line 2
  city CHAR(20),			# city name
  state CHAR(2),			# state or province
  zipcode CHAR(10),			# zip or postal code
  country CHAR(20),			# country name
  seckey CHAR(40),			# security crypto key
  updatedate TIMESTAMP,			# last update date
  createdate TIMESTAMP			# create date
) TYPE = MYISAM;

#
# member : defines members and their attributes
#

create table member
(
  memid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  fname CHAR(12),			# first name
  mname CHAR(12),			# middle name
  lname CHAR(20),			# last name
  company CHAR(60),			# company name
  address1 CHAR(60),			# street address line 1
  address2 CHAR(60),			# street address line 2
  city CHAR(20),			# city name
  state CHAR(2),			# state or province
  zipcode CHAR(10),			# zip or postal code
  country CHAR(20),			# country name
  # contact info
  email1 CHAR(50),			# email address 1
  email2 CHAR(50),			# email address 2
  phoneday CHAR(16),			# day phone
  phoneeve CHAR(16),			# evening phone
  # security info
  userid CHAR(16),			# userid
  passwd CHAR(16),			# password
  secquestion CHAR(50),			# security question
  seqanswer CHAR(20),			# answer to security question
  birthday CHAR(8),			# birthday
  # attributes
  access CHAR(1),			# 0 = no, 1 = guest, 2 = user, 3 = admin
  updatedate TIMESTAMP,			# last update date
  createdate TIMESTAMP,			# create date
  INDEX uidx (userid),			# userid index
  INDEX eidx1 (email1),			# email 1 index
  INDEX eidx2 (email2)			# email 2 index
) TYPE = MYISAM;

#
# item : defines items for sale
#

create table item
(
  itemid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  vendid INT UNSIGNED NOT NULL,		# vendor/drop shipper id
  mfgrid INT UNSIGNED NOT NULL,		# manufacturer id
  itemno CHAR(20),			# item number
  vitemno CHAR(20),			# vendor item number
  title CHAR(60),			# title of item
  stockcode CHAR(2),			# stock code
  catlist CHAR(120),			# list of categories (comma separated)
  recom CHAR(120),			# recommendation categories (comma sep)
  attrib CHAR(120),			# attributes (comma separated)
  packaging CHAR(20),			# packaging nomenclature
  unit CHAR(1),				# 0 = package, 1 = case
  cost DECIMAL(10,2),			# our cost
  price DECIMAL(10,2),			# our price
  retail DECIMAL(10,2),			# reference retail price
  descr TEXT,				# item description
  private CHAR(1),			# 0 = normal, 1 = private item
  # option choices (size, color, etc)
  optname1 CHAR(20),			# option name 1
  optsel1 TEXT,				# option choices 1, semicolon separated
  optname2 CHAR(20),			# option name 2
  optsel2 TEXT,				# option choices 2, semicolon separated
  optname3 CHAR(20),			# option name 3
  optsel3 TEXT,				# option choices 3, semicolon separated
  # shipping info
  shipid INT UNSIGNED,			# shipping classification
  weight DECIMAL(10,2),			# weght in pounds
  # miscellaneous
  picture CHAR(1),			# 0 = no, 1 = has picture(s)
  updatedate TIMESTAMP,			# update date
  createdate TIMESTAMP,			# create date
  # indexes
  UNIQUE idx1 (itemno),			# item number index
  INDEX idx2 (vendid),			# vendor id index
  INDEX idx3 (mfgrid)			# manufacturer id index
) TYPE = MYISAM;

#
# stock : defines stock codes (used by item table)
#

create table stock
(
  stockcode CHAR(2) NOT NULL PRIMARY KEY,
  oos CHAR(1),				# out of stock: 0 = no, 1 = yes
  title CHAR(60),			# descriptive title 
  updatedate TIMESTAMP,			# update date
  createdate TIMESTAMP,			# create date
  INDEX idx1 (oos)			# out of stock index
) TYPE = MYISAM;

#
# category : define categories and their hierarchy
#

create table category
(
  catid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title CHAR(60),			# category title
  url CHAR(60),				# url for category list page
  compcats CHAR(120),			# component categories
  items CHAR(1),			# 0 = no items, 1 = contains items
  visible CHAR(1),			# 0 = invisible, 1 = visible
  descr TEXT,				# category description
  updatedate TIMESTAMP,			# last update date
  createdate TIMESTAMP			# create date
) TYPE = MYISAM;

#
# mfgr : defines manufacturers
#

create table mfgr
(
  mfgrid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title CHAR(60),			# manufacturer name
  url CHAR(60),				# url for manufacturer list page
  descr TEXT,				# manufacturer description
  updatedate TIMESTAMP,			# update date
  createdate TIMESTAMP			# create date
) TYPE = MYISAM;

#
# recom : defines recommendation categories
#

create table recom
(
  recomid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title CHAR(60),			# recommendation title
  url CHAR(60),				# url for recommend list page
  descr TEXT,				# recommendation description
  updatedate TIMESTAMP,			# update date
  createdate TIMESTAMP			# create date
) TYPE = MYISAM;

#
# vendor : defines vendors and drop shippers
#

create table vendor
(
  vendid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  company CHAR(60),			# company name
  address1 CHAR(60),			# street address line 1
  address2 CHAR(60),			# street address line 2
  city CHAR(20),			# city name
  state CHAR(2),			# state or province
  zipcode CHAR(10),			# zip or postal code
  country CHAR(20),			# country name
  # contact info
  name CHAR(50),			# contact name
  email CHAR(50),			# email address
  phone CHAR(16),			# day phone
  fax CHAR(16),				# fax number
  url CHAR(60),				# website
  descr TEXT,				# vendor notes
  # misc
  status CHAR(1),			# vendor = 0, drop shipper= 1
  updatedate TIMESTAMP,			# last update date
  createdate TIMESTAMP			# create date
) TYPE = MYISAM;

#
# session : defines session numbers for cookies
#

create table session
(
  sessid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  period INT UNSIGNED,			# length of time in days
  attrib CHAR(1),			# attribute (general use)
  createdate TIMESTAMP			# create date
) TYPE = MYISAM;

#
# cart : records items in a shopping cart
#

create table cart
(
  cartid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  sessid INT UNSIGNED NOT NULL,		# session id
  itemid INT UNSIGNED NOT NULL,		# item id
  itemno CHAR(20),			# item number
  title CHAR(60),			# title of item
  optname1 CHAR(20),			# option name 1
  optval1 CHAR(25),			# option choice 1
  optname2 CHAR(20),			# option name 2
  optval2 CHAR(25),			# option choice 2
  optname3 CHAR(20),			# option name 3
  optval3 CHAR(25),			# option choice 3
  quantity INT UNSIGNED,		# quantity
  price DECIMAL(10,2),			# price of each item
  updatedate TIMESTAMP,			# update date
  INDEX idx1 (sessid),			# item index
  INDEX idx2 (itemno)			# item number index
) TYPE = MYISAM;

#
# shipto : order shipping info
#

create table shipto
(
  shiptoid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  orderid INT UNSIGNED NOT NULL,	# order id
  sessid INT UNSIGNED,			# session id
  memid INT UNSIGNED,			# member id
  # ship to
  fname CHAR(12),			# first name
  mname CHAR(12),			# middle name
  lname CHAR(20),			# last name
  company CHAR(60),			# company name
  address1 CHAR(60),			# street address line 1
  address2 CHAR(60),			# street address line 2
  city CHAR(20),			# city name
  state CHAR(2),			# state or province
  zipcode CHAR(10),			# zip or postal code
  country CHAR(20),			# country name
  # contact info
  email1 CHAR(50),			# email address 1
  email2 CHAR(50),			# email address 2
  phoneday CHAR(16),			# day phone
  phoneeve CHAR(16),			# evening phone
  shipnotes TEXT,			# special shipping instructions
  # shipment info
  status CHAR(1),			# 0=in process, 1=shipped, 2=backorder
  dateship CHAR(16),			# date shipped
  method CHAR(20),			# shipping method
  trackno CHAR(20),			# tracking number
  # payment info
  updatedate TIMESTAMP,			# last update date
  createdate TIMESTAMP,			# create date
  UNIQUE idx1 (orderid),		# order id index
  UNIQUE idx2 (sessid)			# session id index
) TYPE = MYISAM;

#
# billto : order billing data
#

create table billto
(
  billid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  orderid INT UNSIGNED NOT NULL,	# order id
  sessid INT UNSIGNED,			# session id
  memid INT UNSIGNED,			# member id
  # billing info
  name CHAR(40),			# name
  company CHAR(40),			# company name
  address1 CHAR(60),			# street address line 1
  address2 CHAR(60),			# street address line 2
  city CHAR(20),			# city name
  state CHAR(2),			# state or province
  zipcode CHAR(10),			# zip or postal code
  country CHAR(20),			# country name
  # credit card info
  # V=visa, M=mc, D=discover, A=amex, O=money order, C=cash, K=check, I=invoice
  paymeth CHAR(1),			# payment method
  ccnumber CHAR(16),			# credit card number
  ccseccode CHAR(4),			# credit card security code (4 digits)
  ccexpmm CHAR(2),			# credit card expiration month
  ccexpyy CHAR(2),			# credit card expiration year
  cctransid CHAR(20),			# credit card transaction id
  approval CHAR(16),			# approval code
  # balance info
  orderamt DECIMAL(10,2),		# original order amount (w/o refund)
  paid DECIMAL(10,2),			# amount paid (running balance)
  due DECIMAL(10,2),			# amount due (running balance)
  refund DECIMAL(10,2),			# refund amount (if any)
  notes TEXT,				# notes
  updatedate TIMESTAMP,			# update date
  createdate TIMESTAMP,			# create date
  UNIQUE idx1 (orderid),		# order id index
  UNIQUE idx2 (sessid)			# session id index
) TYPE = MYISAM;

#
# orders : defines an order
#

create table orders
(
  orderid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  sessid INT UNSIGNED,			# session id
  memid INT UNSIGNED,			# member id
  itemtot DECIMAL(10,2),		# item amount total
  discount DECIMAL(10,2),		# item discount total
  tax DECIMAL(10,2),			# sales tax
  shipcost DECIMAL(10,2),		# shipping cost
  total DECIMAL(10,2),			# total cost (w/o refunds)
  refund DECIMAL(10,2),			# refund amount (if any)
  # status: 0=new, 1=accepted, 2=hold (not paid), 3=xmit, 4=shipped, 5=cancel
  status CHAR(1),			# status of order
  updatedate TIMESTAMP,			# last update date
  createdate TIMESTAMP,			# create date
  UNIQUE idx1 (sessid),			# userid index
  INDEX idx2 (memid)			# member id index
) TYPE = MYISAM;

#
# orderd : order detail records
#

create table orderd
(
  orderdid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  orderid INT UNSIGNED NOT NULL,	# master order number
  sessid INT UNSIGNED,			# session id
  itemid INT UNSIGNED NOT NULL,		# item id
  itemno CHAR(20),			# item number
  title CHAR(60),			# title of item
  optname1 CHAR(20),			# option name 1
  optval1 CHAR(25),			# option choice 1
  optname2 CHAR(20),			# option name 2
  optval2 CHAR(25),			# option choice 2
  optname3 CHAR(20),			# option name 3
  optval3 CHAR(25),			# option choice 3
  quantity INT UNSIGNED,		# quantity
  price DECIMAL(10,2),			# price of each item
  updatedate TIMESTAMP,			# update date
  createdate TIMESTAMP,			# create date
  INDEX idx1 (orderid),			# order id index
  INDEX idx2 (sessid),			# session id index
  INDEX idx3 (itemno)			# item number index
) TYPE = MYISAM;

#
# xmit : drop ship order transmit record
#

create table xmit
(
  xmitid INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  orderid INT UNSIGNED NOT NULL,	# master order number
  vendid INT UNSIGNED NOT NULL,		# vendor id
  cost DECIMAL(10,2),			# cost of order
  status CHAR(1),			# 0=new,1=backorder,2=shipped,3=out
  updatedate TIMESTAMP,			# update date
  createdate TIMESTAMP,			# create date
  INDEX idx1 (orderid),			# order id index
  INDEX idx2 (vendid)			# vendor id index
) TYPE = MYISAM;

# display the results of this script
show tables;