Database Design

Organise Database

Following steps are involved for database creation in MySql.

Load MySql with user name and password as root. Load the database scripts with command source with file path as per the snapshot below.

We will create database as cart and then create tables. Once the tables are created we will insert  some master data required by the program. Two scripts are used here.

1. dbcreate.txt . This script will create database and create tables.
2. dbinsert.txt . This script will insert some master data in tables.

Change the database to cart and check the tables created with command show tables as per snapshot below.

Download Database Script.

Following are the two scripts :dbcreate.txt and dbinsert.txt

Script:dbcreate.txt

This script will create the database cart (It may thrown error if already exists) . Drop the tables if already exists and create new tables.


-- Drop Database if already existing
-- drop database cart;

-- Create database as Cart
create database cart;

-- Use database Cart for Creation of Database.
use cart;

-- Drop all the tables if already exiting
drop table  menu;  --  This table will have data of all generalized menu option for each role
drop table  usernm; -- This table will have data of users with name,password, hint with answer and address
drop table  system;  -- This table will have name of company with address and bottom page message for which website is created
drop tables items;   -- This table will have list of Items to select with its details
drop tables catg_mas;   --  This table will have data for categories to choose and filter items
drop tables catg_details;  -- This table will have data for various offers
drop tables items_reviews;  -- This table will have data for feedback
drop tables items_payment_options;  -- This table will have data for payment options like cod,netbanking
drop tables items_delivery_available_pin ; -- This table will have data where all the items can be dispatched
drop table cart;
drop table payments;
drop table schedule;
drop table schedule_mas;
drop table seller;

-- Create Menu with options.
create table menu (id int(4) UNSIGNED AUTO_INCREMENT PRIMARY KEY,menucode varchar(5),menu_short_name varchar(10),menu_long_name varchar(20),menu_run_option varchar(50),menu_order int(4),role varchar(15));

-- Create User and Login details
create table usernm (id int(4) unsigned auto_increment primary key,user varchar(8),password varchar(8),role varchar(15),create_date date,hint varchar(50),hint_ans varchar(15),user_name varchar(25),user_email varchar(50),gender varchar(1),dob date,mobile int(10),address varchar(100));

-- Create Company and System Details
create table system (id int(4) unsigned auto_increment primary key,company_name varchar(20),add1 varchar(25),add3 varchar(25), bottom_message varchar(25));

-- Create Items to sell
create table items (id int(4) unsigned auto_increment primary key,
item_category_code varchar(10),
item_code varchar(10),
item_short_desc varchar(25),
item_long_desc varchar(50),
item_image varchar(20),
item_color varchar(10),
item_warranty int(4),
item_size varchar(10),
item_value double(8,2),
delivery_days int(4));

-- Create Items to sell by seller
create table seller (id int(4) unsigned auto_increment primary key,
seller_code varchar(10),
seller_name varchar(30),
seller_address varchar(100),
seller_mob int(10),
item_code varchar(10),
item_value double(8,2));

-- Create item delivery and pin availability
create table items_delivery_available_pin (id int(4) unsigned auto_increment primary key,
item_code varchar(10),
pin varchar(10));


-- Create table for payment options
create table items_payment_options (id int(4) unsigned auto_increment primary key,
item_code varchar(10),
payment_options varchar(10));

-- Create table for delivery options based on pin code
create table items_reviews (id int(4) unsigned auto_increment primary key,
item_code varchar(10),
reviews varchar(50),
customer_code varchar
(10),
dated date);

-- Create table for category to filter items
create table catg_mas (id int(4) unsigned auto_increment primary key,
catg_code varchar(10),
catg_discount_scheme varchar(10));

-- Create table for category details for offers to be provided
create table catg_details (id int(4) unsigned auto_increment primary key,
discounts_provider_name varchar(10),
discounts_details varchar(50),
discounts_value int(4),
discounts_pers int(4));

-- Create table to store data for Items saved in cart selected by user for each seller
create table cart (id int(4) unsigned auto_increment primary key,
usercode varchar(8),
shopper varchar(8),
item_code varchar(10),
item_value double(8,2),
cart_date date);

-- Create table payments made with status
create table payments (id int(4) unsigned auto_increment primary key,
usercode varchar(8),
shopper varchar(8),
item_code varchar(10),
payment_option varchar(10),
payment_date date,
payment_status varchar(8));

-- Create table to save data for schedule/movement of Item lying at different stages
create table schedule (id int(4) unsigned auto_increment primary key,
usercode varchar(8),
shopper varchar(8),
item_code varchar(10),
schedule_order int(4),
schedule_type varchar(30),
schedule_date date);

-- Create table as a master for stages of dispatch
create table schedule_mas (id int(4) unsigned auto_increment primary key,
schedule_order int(4),
schedule_type varchar(30));





Script: dbinsert.txt

This script will insert some master data required my the project.

use cart;
insert into usernm values (1,'aj','aj','c','2015/08/08','pin code','110051','ajay','daviral06@gmail.com','m','1962/05/07','9810998788','abc enclave,Dli,pin-110051');
insert into usernm values (2,'admin','admin','a','2015/08/08','pin code','110051','vijay','vijay06@gmail.com','m','1984/05/07','9816666788','orange enclave,Dli,pin-119999');
insert into usernm values (3,'supplier','abc','s','2015/08/08','pin code','110051','suplier','suplier06@gmail.com','m','1979/05/07','9812222788','orange enclave,Dli,pin-115555');


insert into menu values(1,'1','Master','List Items','/faces/items/List.xhtml',1,'a');
insert into menu values(2,'2','Master','List Discounts','/faces/catgDetails/List.xhtml',2,'a');
insert into menu values(3,'3','Master','Delivery  Options','/faces/itemsDeliveryAvailablePin/List.xhtml',3,'a');
insert into menu values(4,'4','Master','List Payments Mode ','/faces/itemsPaymentOptions/List.xhtml',4,'a');
insert into menu values(5,'5','Customer','Review Cartlogs ','/faces/cartview/itemsList.xhtml',5,'c');
insert into menu values(6,'6','Seller','Seller Details','/faces/seller/selleritems.xhtml',6,'a,s');
insert into menu values(7,'7','Admin','Change Menu','/faces/menu/List.xhtml',7,'a');
insert into menu values(8,'8','Admin','Create and Edit User','/faces/usernm/List.xhtml',8,'a');
insert into menu values(9,'9','Master','List Category','/faces/catgMas/List.xhtml',9,'a');



insert into system values(1,"ABC Company","Address - xyz...","Address2 -mnop","(c) Copyright ..");
insert into items values(1,"stationary","pen","pen","Pen","","red",10,"1 nos",10.00,10);
insert into items values(2,"stationary","pen","pen","Pen","","red",10,"1 nos",12.00,10);
insert into items values(3,"electronics","laptop","laptop","laptop","","red",10,"1 nos",20000.00,10);

insert into catg_mas values (1,"stationary","axis10");
insert into catg_mas values (2,"electronics","axis20");
insert into catg_mas values (3,"fashion","axis20");
insert into catg_mas values (4,"All","axis20");


insert into catg_details values(1,"Ax Bank","10% discounts min value 1000",0,10);
insert into catg_details values(2,"HD Bank","20% discounts min value 2000",0,20);

insert into items_reviews values(1,"pen","good product","ajay","2015-01-10");
insert into items_reviews values(2,"laptop","not a good product","vijay","2015-01-01");

insert into items_payment_options values(1,"pen","cod");
insert into items_payment_options values(2,"pen","netbanking");

insert into items_delivery_available_pin values(1,"pen","110051");

insert into schedule_mas values(1,1,"Order Booked");
insert into schedule_mas values(2,2,"Order Placed to Vendor");
insert into schedule_mas values(3,3,"Order Packed by Vendor");
insert into schedule_mas values(4,4,"Order Dispatched");
insert into schedule_mas values(5,5,"Recieved by Courior");
insert into schedule_mas values(6,6,"Out for Delivery");
insert into schedule_mas values(7,7,"Item Received");