The database

Home Contents

Please email any bug reports, comments or suggestions to ExperLog's Online Support

Why do we need a database ?

ExperSHOP Lite is backed on an SQL database.
The database is used to store all the information concerning:

The departments and products in the shop
The customer and order informations

Which database can you use ?

Any database that supports a JDBC driver.
Note that all database accessible via ODBC are accessible via JDBC.

How to setup your database ?

You need to create some tables in your database for ExperSHOP Lite: see the next paragraph, concerning the database schema.

Database schema

The database schema describes the database tables, with all the fields they contain (for example, a "Product" table will contain fields for the product name, the price, etc...)

ExperShop Lite allows you to change the database schema so it fits your needs: for example, you can easily add new fields to the EProduct table so it reflects the structure of the products you sell.

The table names must not change.
Some fields in the tables are mandatory and should not be removed or renamed : particularly the "Id" fields (like "ProdId", the product reference, in the EProduct table).

By default, ExperSHOP uses the following database schema, expressed in SQL:



create table EDepartment (
	DeptId		varchar(20) primary key,
	Name		varchar(64) NOT NULL,
	DeptImage	varchar(128));

create table EProduct (
	ProdId		varchar(20) primary key,
        Category	varchar(20),
        Name            text NOT NULL,
	Brand		varchar(32),
	Model		varchar(32),
	Descrip		text,
	Price		double NOT NULL,
        InStock         integer,
        StockLimit      integer,
	ProdImage	varchar(80),
	LargeImage	varchar(80),
	DeptId		varchar(20),
        Publish		varchar(1));

create table ECustomer (
	CustId		varchar(20) primary key,
	LastName	varchar(30) NOT NULL,
	FirstName	varchar(30) NOT NULL,
        Password        varchar(20),
	Address		varchar(120),	
	City		varchar(32),
	ZipCode		varchar(20),
	State		varchar(32),
	Country		varchar(32),	
	Fax		varchar(32),	
	Tel		varchar(32),
	Email		varchar(64));

create table ECustOrder (
	OrdId		varchar(20) primary key,
	CustId		varchar(20),
	AffId		varchar(20),
	WlId		varchar(20),
        OrderTime	long,
        TotalPrice	double NOT NULL,
        Tax		double NOT NULL,
        ShippingCost	double NOT NULL,
        ShipName	varchar(64),
	ShipAddress	varchar(120),	
	ShipCity	varchar(32),
	ShipZipCode	varchar(20),
	ShipState	varchar(32),
	ShipCountry	varchar(32),	
        ShipOptions	text,
	Message         text,
	Status		varchar(10),
        PayStatus	varchar(10),
        PayInfo		varchar(32),
        CcNum		varchar(20),
	CcExpDate	varchar(12),
        CcName		varchar(32));

create table EProdOrder (
	OrdId		varchar(20),
	ItemId varchar(128) NOT NULL,
	Rank integer,
	Superior varchar(20) NOT NULL,
	ProdId		varchar(20),
	Name		text,
	Price		double NOT NULL,
	Qty		integer NOT NULL,
	Options		varchar(80),
	Config          varchar(128),
	primary key (OrdId, ItemId));

create table ECartSaver (
        CartId varchar(32),
        Item text,
        ExpDate long);

create table EAffiliate (
	AffId varchar(32),
	Name varchar(64),
	Password varchar(16),
	Email varchar(64),
	Miles double
);

Just copy/paste the schema in a text file, and create the tables in your favourite database : this may require minor modifications in the schema, because of minor SQL syntax differences between databases.