NAME

Nitesi::Database::Schema - Database schema for Nitesi

DESCRIPTION

This document describes the proposed database schema for Nitesi, the Open Source Shop Machine.

The CREATE TABLE statements are written for MySQL, but should work with small adjustments for PostgreSQL too.

PRODUCTS

Products are stored in the products table.

products table

CREATE TABLE products (
  sku varchar(32) NOT NULL PRIMARY KEY,
  name varchar(255) NOT NULL DEFAULT '',
  short_description varchar(500) NOT NULL DEFAULT '',
  description text NOT NULL DEFAULT '',
  price decimal(10,2) NOT NULL DEFAULT 0,
  uri varchar(255) NOT NULL DEFAULT '',
  weight integer NOT NULL DEFAULT 0,
  priority integer NOT NULL DEFAULT 0,
  gtin varchar(32) NOT NULL DEFAULT '',
  canonical_sku varchar(32) NOT NULL DEFAULT '',
  status varchar(32) NOT NULL DEFAULT '',
  inactive boolean NOT NULL DEFAULT FALSE
);

sku

Unique product identifier

name

Product name.

short_description

Short description of the product.

description

Full description of the product.

price

Product price.

uri

Link to product.

weight

Product weight in grams.

priority

The product priority is used for sorting products on search results and category listings.

gtin

Global trade item number (GTIN). ISBN, EAN and UPC numbers can be expressed as GTIN, see http://en.wikipedia.org/wiki/Global_Trade_Item_Number.

canonical_sku

The canonical SKU is used for product variants, e.g. different size and color.

status

The product status. Possible values are:

available: Product can be delivered immediately.
short wait: Product can be delivered in a few days.
long wait: Product can be delivered, but it takes longer than a few days.
unknown: Product can be delivered, but we have no idea how long it takes.
not available: Product is sold out or discontinued.

inactive

Inactive products are excluded from search results and category listings.

product_attributes table

CREATE TABLE product_attributes (
  code serial NOT NULL PRIMARY KEY,
  sku varchar(32) NOT NULL,
  name varchar(32) NOT NULL,
  value text NOT NULL default '',
  original_sku varchar(32) NOT NULL default ''
) CHARACTER SET utf8;

CREATE INDEX product_attributes_sku ON product_attributes (sku);

sku: Unique product identifier from products table.
name: Name of the attribute, e.g. color.
value: Value of the attribute, e.g. red.
original_sku: Product identifier for the master attribute.

inventory table

CREATE TABLE inventory (
   sku varchar(32) NOT NULL PRIMARY KEY,
   quantity integer NOT NULL DEFAULT 0,
   in_stock boolean,
);

sku: Unique product identifier from products table.
quantity: Available quantity.
in_stock: Whether to show item as in_stock or not.

Menus and (product) categories are stored in the navigation table.

CREATE TABLE navigation (
  code serial NOT NULL,
  uri varchar(255) NOT NULL DEFAULT '',
  type varchar(32) NOT NULL DEFAULT '',
  scope varchar(32) NOT NULL DEFAULT '',
  name varchar(255) NOT NULL DEFAULT '',
  description text NOT NULL DEFAULT '',
  template varchar(255) NOT NULL DEFAULT '',
  language varchar(8) NOT NULL DEFAULT '',
  alias integer NOT NULL DEFAULT 0,
  parent integer NOT NULL DEFAULT 0,
  priority integer NOT NULL DEFAULT 0,
  count integer NOT NULL DEFAULT 0,
  inactive boolean NOT NULL default FALSE,
  entered timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(code),
  UNIQUE(uri)
);

uri

URL for the link to the navigation element. Starts with the URL of the parent navigation element.

E.g. for parent element "Europe" and child element "Germany" you get as uri values:

Europe
Europe/Germany

type

Navigation type, e.g. menu or category.

scope

Scope related to type, menu name for menus or categorization (brand, star) for categories.

template

Template used for this navigation element. This is used usually in conjunction with menu elements.

language

Language for this navigation element.

alias

Pointer to the original navigation element.

parent

Pointer to the parent navigation element.

count

Number of active products belonging to this navigation entry.

navigation_products table

CREATE TABLE navigation_products (
  sku varchar(32) NOT NULL,
  navigation integer NOT NULL,
  type varchar(16) NOT NULL DEFAULT '',
  key(sku,navigation)
);

MEDIA

Media are stored in the media table.

`media` table

create table media (
   code serial NOT NULL,
   file varchar(255) NOT NULL DEFAULT '',
   uri varchar(255) NOT NULL DEFAULT '',
   mime_type varchar(255) NOT NULL DEFAULT '',
   label varchar(255) NOT NULL DEFAULT '',
   author integer NOT NULL DEFAULT 0,
   created datetime NOT NULL,
   modified datetime NOT NULL,
   inactive boolean NOT NULL DEFAULT FALSE,
   primary key(code)
--      unique(file),
--      unique(uri)
);

`media_types` table

create table media_types (
   code serial NOT NULL,
   type varchar(32) NOT NULL,
   scope varchar(32) NOT NULL,
   unique(type,scope)
);

insert into media_types (type,scope) values ('detail', 'store');
insert into media_types (type,scope) values ('thumb', 'store');
insert into media_types (type,scope) values ('cart', 'store');

`media_products` table

create table media_products (
   code serial NOT NULL,
   media integer NOT NULL,
   sku varchar(32) NOT NULL,
   KEY (sku)
);

`media_display` table

create table media_display (
   code serial NOT NULL,
   media integer NOT NULL,
   sku varchar(32) NOT NULL,
   type integer NOT NULL,
   KEY (sku)
);

MERCHANDISING

Merchandising tables.

CREATE TABLE merchandising_products (
  code serial NOT NULL,
  sku character varying(32) NOT NULL DEFAULT '',
  sku_related character varying(32) NOT NULL DEFAULT '',
  type character varying(32) NOT NULL DEFAULT '',
  primary key(code)
);

CREATE TABLE merchandising_attributes (
  code serial NOT NULL,
  merchandising integer NOT NULL default 0,
  name varchar(32) NOT NULL,
  value text NOT NULL default '',
  primary key(code)
);

PRICING

`group_pricing` table

CREATE TABLE group_pricing (
    code serial NOT NULL,
    sku character varying(32) NOT NULL,
    quantity integer NOT NULL DEFAULT 0,
    rid integer NOT NULL DEFAULT 0,
    price decimal(10,2) NOT NULL DEFAULT 0,
    primary key(code),
    key(sku)
);

CARTS

Carts are stored in the carts and carts_products table.

`carts` table

 CREATE TABLE carts (
   code serial NOT NULL,
   name character varying(255) DEFAULT '' NOT NULL,
   uid integer DEFAULT 0 NOT NULL,
   session_id character varying(255) DEFAULT '' NOT NULL,
   created integer DEFAULT 0 NOT NULL,
   last_modified integer DEFAULT 0 NOT NULL,
   type character varying(32) DEFAULT '' NOT NULL,
   approved boolean,
   status character varying(32) DEFAULT '' NOT NULL,
   primary key(code)
);

cart_products

CREATE TABLE cart_products (
  cart integer NOT NULL,
  sku character varying(32) NOT NULL,
  "position" integer NOT NULL,
  quantity integer DEFAULT 1 NOT NULL,
  priority integer DEFAULT 0 NOT NULL
);

USERS, ROLES, PERMISSIONS

users

CREATE TABLE users (
  uid serial primary key,
  username varchar(255) NOT NULL,
  email varchar(255) NOT NULL DEFAULT '',
  password varchar(255) NOT NULL DEFAULT '',
  first_name varchar(255) NOT NULL DEFAULT '',
  last_name varchar(255) NOT NULL DEFAULT '',
  last_login integer NOT NULL DEFAULT 0,
  created datetime NOT NULL,
  modified datetime NOT NULL,
  inactive boolean NOT NULL DEFAULT FALSE
);

uid: Numeric primary key for users.
username: User name (usually lowercase of email).
email: Email address.
password: Encrypted password.
last_login: Time of last login
created: Time of account creation.

user_attributes

CREATE TABLE user_attributes (
  code serial NOT NULL,
  uid integer NOT NULL,
  name varchar(32) NOT NULL,
  value text NOT NULL default '',
  primary key(code),
  key(uid)
);

Attributes to be stored in user_attributes are:

aid_shipping: Default shipping address identifier.
aid_billing: Default billing address identifier.

roles

CREATE TABLE roles (
  rid serial primary key,
  name varchar(32) NOT NULL,
  label varchar(255) NOT NULL
);

INSERT INTO roles (rid,name,label) VALUES (1, 'anonymous', 'Anonymous Users');
INSERT INTO roles (rid,name,label) VALUES (2, 'authenticated', 'Authenticated Users');

rid: Numeric primary key for roles.
name: Role name.
label: Role label (for display only).

user_roles

CREATE TABLE user_roles (
  uid integer DEFAULT 0 NOT NULL,
  rid integer DEFAULT 0 NOT NULL,
  PRIMARY KEY (uid, rid)
);

CREATE INDEX idx_user_roles_rid ON user_roles (rid);

uid: Foreign key for user.
rid: Foreign key for role.

permissions

CREATE TABLE permissions (
  rid integer not null default 0,
  uid integer not null default 0,
  perm varchar(255) not null default ''
);

INSERT INTO permissions (rid,perm) VALUES (1,'anonymous');
INSERT INTO permissions (rid,perm) VALUES (2,'authenticated');

Permissions are you usually granted to rules, but in somes cases you may want to grant a permission to a specific user.

Please set either rid or uid and use 0 as value for the other in a single record.

rid: Foreign key for role.
uid: Foreign key for user.
perm: Permission, e.g. view_cart, add_user.

ADDRESSES

addresses

CREATE TABLE addresses (
  aid serial NOT NULL,
  uid integer NOT NULL DEFAULT 0,
  type varchar(16) NOT NULL DEFAULT '',
  archived boolean NOT NULL DEFAULT FALSE,
  first_name varchar(255) NOT NULL DEFAULT '',
  last_name varchar(255) NOT NULL DEFAULT '',
  company varchar(255) NOT NULL DEFAULT '',
  street_address varchar(255) NOT NULL DEFAULT '',
  zip varchar(255) NOT NULL DEFAULT '',
  city varchar(255) NOT NULL DEFAULT '',
  phone varchar(32) NOT NULL DEFAULT '',
  state_code char(2) NOT NULL DEFAULT '',
  country_code char(2) NOT NULL DEFAULT '',
  created datetime NOT NULL,
  modified datetime NOT NULL,
  CONSTRAINT transactions_pkey PRIMARY KEY (aid)
);

uid: Foreign key for user, 0 for anonymous users.
type: Address type, e.g. shipping or billing.
archived: Set for addresses used in the past.
first_name: First name of person attached to this address.
last_name: Last name of person attached to this address.
company: Company name attached to this address.
street_address: Street address, including house number.
zip: Zip code.
city: City.
state_code: State code.
country_code: Country code.

TRANSACTIONS

transactions

create table transactions (
  code serial not null,
  order_number varchar(24) NOT NULL DEFAULT '',
  order_date timestamp,
  uid integer NOT NULL DEFAULT 0,
  email varchar(255) NOT NULL DEFAULT '',
  aid_shipping integer NOT NULL DEFAULT 0,
  aid_billing integer NOT NULL DEFAULT 0,
  weight integer NOT NULL DEFAULT 0,
  payment_method varchar(255) NOT NULL DEFAULT '',
  payment_code varchar(255) NOT NULL DEFAULT '',
  payment_status varchar(255) NOT NULL DEFAULT '',
  shipping_method varchar(255) NOT NULL DEFAULT '',
  shipping_code varchar(255) NOT NULL DEFAULT '',
  subtotal numeric(11,2) NOT NULL DEFAULT 0,
  shipping numeric(11,2) NOT NULL DEFAULT 0,
  handling numeric(11,2) NOT NULL DEFAULT 0,
  salestax numeric(11,2) NOT NULL DEFAULT 0,
  total_cost numeric(11,2) NOT NULL DEFAULT 0,
  status varchar(24) NOT NULL DEFAULT '',
  CONSTRAINT transactions_pkey PRIMARY KEY (code)
);

payment_status

Possible values are:

pending: Payment is pending.
success: Payment has been successful.
failure: Payment has been failed.
canceled: Payment has been canceled.

shipping_method

Shipping method for the order.

shipping_code

Shipping ID assigned by shipping company.

subtotal

Subtotal of the order (sum of product prices).

shipping

Shipping cost of the order.

handling

Other fees applied to the order.

salestax

Salestax for the order.

total_cost

Total amount of the order.

status

Status of the order.

orderline

create table orderline (
  code serial not null,
  order_number varchar(24) NOT NULL DEFAULT '',
  position integer NOT NULL DEFAULT 0,
  sku varchar(32) NOT NULL PRIMARY KEY,
  name varchar(255) NOT NULL DEFAULT '',
  short_description varchar(500) NOT NULL DEFAULT '',
  description text NOT NULL DEFAULT '',
  weight integer NOT NULL DEFAULT 0,
  quantity integer,
  quantity_shipped integer,
  shipping_method varchar(255) NOT NULL DEFAULT '',
  shipping_code varchar(255) NOT NULL DEFAULT '',
  price numeric(10,2) NOT NULL DEFAULT 0,
  subtotal numeric(11,2) NOT NULL DEFAULT 0,
  shipping numeric(11,2) NOT NULL DEFAULT 0,
  handling numeric(11,2) NOT NULL DEFAULT 0,
  salestax numeric(11,2) NOT NULL DEFAULT 0,
  status varchar(24) NOT NULL DEFAULT ''
);

The fields sku, name, short_description, description, price and weight are the values for the product at the time of order.

order_number: Reference to order number in transactions.
position: Position of ordered item (1, ...).
quantity: Quantity of ordered item.
quantity_shipped: Shipped quantity of ordered item.
shipping_method: Shipping method for the ordered item.
shipping_code: Shipping ID assigned by shipping company.
subtotal: Subtotal of the ordered item (sum of product price).
shipping: Shipping cost of the ordered item.
handling: Other fees applied to the ordered item.
salestax: Salestax for the ordered item.
status: Status of the ordered item.

payment_transactions

CREATE TABLE payment_transactions (
  code serial NOT NULL PRIMARY KEY,
  payment_mode varchar(32) NOT NULL default '',
  payment_action varchar(32) NOT NULL default '',
  payment_id varchar(32) NOT NULL default '',
  auth_code varchar(255) NOT NULL default '',
  session_id varchar(255) NOT NULL default '',
  order_number varchar(24) NOT NULL default '',
  amount numeric(11,2) NOT NULL DEFAULT 0,
  status varchar(32) NOT NULL default '',
  payment_session_id varchar(255) NOT NULL default '',
  payment_error_code varchar(32) NOT NULL default '',
  payment_error_message text NOT NULL default '',
  created datetime default NULL,
  last_modified datetime default NULL
);

payment_mode: Payment gateway.
payment_id: Payment id retrieved from payment server.
auth_code: Authentication code retrieved from payment server.
session_id: Session id used in the shop.
order_number: Order number from transactions table.
amount: Amount passed to payment server.
payment_session_id: Session id retrieved from payment server, e.g. for IPayment gateway.

SETTINGS

Settings stored in the database, used to complement the settings retrieved from the web framework configuration, e.g. Dancer::Config.

settings

CREATE TABLE settings (
  code serial primary key,
  scope varchar(32) NOT NULL,
  site varchar(32) NOT NULL default '',
  name varchar(32) NOT NULL,
  value text NOT NULL,
  category varchar(32) NOT NULL default ''
);

CREATE INDEX settings_scope ON settings (scope);

SESSIONS

Sessions stored in the database (optional).

sessions

CREATE TABLE sessions (
  code varchar(255) not null primary key,
  session_data text not null,
  session_hash text not null,
  created datetime not null,
  modified timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

OTHER

path_redirect

To install Nitesi::DBI, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Nitesi::DBI

CPAN shell

perl -MCPAN -e shell
install Nitesi::DBI

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

DESCRIPTION

PRODUCTS

products table

product_attributes table

inventory table

NAVIGATION

navigation table

navigation_products table

MEDIA

`media` table

`media_types` table

`media_products` table

`media_display` table

MERCHANDISING

PRICING

`group_pricing` table

CARTS

`carts` table

cart_products

USERS, ROLES, PERMISSIONS

users

user_attributes

roles

user_roles

permissions

ADDRESSES

addresses

TRANSACTIONS

transactions

orderline

payment_transactions

SETTINGS

settings

SESSIONS

sessions

OTHER

path_redirect

NAME

DESCRIPTION

PRODUCTS

products table

product_attributes table

inventory table

NAVIGATION

navigation table

navigation_products table

MEDIA

media table

media_types table

media_products table

media_display table

MERCHANDISING

PRICING

group_pricing table

CARTS

carts table

cart_products

USERS, ROLES, PERMISSIONS

users

user_attributes

roles

user_roles

permissions

ADDRESSES

addresses

TRANSACTIONS

transactions

orderline

payment_transactions

SETTINGS

settings

SESSIONS

sessions

OTHER

path_redirect

Module Install Instructions

`media` table

`media_types` table

`media_products` table

`media_display` table

`group_pricing` table

`carts` table