The database


Home Contents

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


Why do we need a database ?

ExperSHOP is backed on an SQL database.
The database is used to store all the information concerning the application; For example, if it is an e-commerce shop, the database may contain:
  • 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: 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 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 (here, the MySQL schema) :


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

  create table ECategory (
	CatId		varchar(20) primary key,
	DeptId		varchar(20),
	Category	varchar(32) NOT NULL);

  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),
	OrderTime	decimal(16,0),
        TotalPrice      double NOT NULL,
        Tax             double NOT NULL,
        ShippingCost    double NOT NULL,
        Discount        double,
        DiscountCode    varchar(64),
        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) NOT NULL,
	ProdId		varchar(20) NOT NULL,
	Name		text,
	Price		double NOT NULL,
	Qty		integer NOT NULL,
	Options		varchar(80),
	Config          varchar(128),
	primary key (OrdId, ProdId));

  create table ECartSaver (
        CartId varchar(32),
        Item text,
        Ityp varchar(1),
        ExpDate decimal(16,0));

  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.