/ 
Nitesi-DBI-0.0050
River stage one • 1 direct dependent • 1 total dependent
/ Nitesi::Database::Schema

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 '',
  description text NOT NULL DEFAULT '',
  price decimal(10,2) NOT NULL DEFAULT 0,
  weight integer NOT NULL DEFAULT 0,
  priority integer NOT NULL DEFAULT 0,
  inactive boolean NOT NULL DEFAULT FALSE
);
sku

Unique product identifier

name

Product name.

description

Product description.

price

Product price.

weight

Product weight in grams.

priority

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

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.

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

CREATE TABLE navigation (
  code serial NOT NULL PRIMARY KEY,
  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 '',
  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
);
uri

Full or relative URL.

type

Navigation type, e.g. menu or category.

scope

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

count

Number of active products belonging to this navigation entry.

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

CARTS

Carts are stored in the carts and carts_products table.

carts table

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

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(32) NOT NULL,
  email varchar(255) NOT NULL DEFAULT '',
  password varchar(255) NOT NULL DEFAULT '',
  last_login integer NOT NULL DEFAULT 0,
  created integer NOT NULL DEFAULT 0
);
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.

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,
  CONSTRAINT user_roles_pkey 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 '',
  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 '',
  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.

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,
  payment_method varchar(255) NOT NULL DEFAULT '',
  payment_code 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)
);

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);

OTHER

path_redirect