In this tutorial you will learn how to design a database for shopping cart application

Database Design

The database of shopping cart application is designed in MySql. To run the shopping cart application you first need to install and design database as given below.

write the following command to create a database

create database if not exists `scart`;
USE `scart`;

Then design the tables as given

This table contains the admin information such as his id, password and it is used during login of admin for his validation.

Adminuser tableADS_TO_REPLACE_1

CREATE TABLE `adminuser` (
  `id` int(11) NOT NULL auto_increment,
  `userid` varchar(40) NOT NULL default '',
  `password` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `userid` (`userid`)
);

This table contains the category of the product defined by the admin. He/She can define a new category also.

Category table

CREATE TABLE `category` (
  `categoryid` int(11) NOT NULL auto_increment,
  `categoryname` varchar(60) default NULL,
  PRIMARY KEY  (`categoryid`)
);

This table contains the product configuration details. Such as which it belongs what is the address of the production company, email, phone etc. It is used by the admin.ADS_TO_REPLACE_2

Config table

CREATE TABLE `config` (
  `id` int(11) NOT NULL auto_increment,
  `storename` varchar(50) NOT NULL default '',
  `storeurl` varchar(50) NOT NULL default '',
  `ordernoticeemail` varchar(50) NOT NULL default '',
  `cursymbol` varchar(6) NOT NULL default '',
  `curcode` varchar(6) NOT NULL default '',
  `aboutustext` varchar(200) NOT NULL,
  `shipdeltext` varchar(250) NOT NULL,
  PRIMARY KEY  (`id`)
);

This table contain the information of the orders which has been full filled.

Orderitems tableADS_TO_REPLACE_3

CREATE TABLE `orderitems` (
  `productid` int(11) NOT NULL default '0',
  `orderid` int(11) NOT NULL default '0',
  `name` char(255) default NULL,
  `price` float default NULL,
  `quantity` int(11) default NULL,
  PRIMARY KEY  (`orderid`)
);

This table contains the information of the order comes from the customers and their detail..

Orders Table

CREATE TABLE `orders` (
  `orderid` int(11) NOT NULL auto_increment,
  `ordertime` datetime default NULL,
  `custfirstname` varchar(30) default NULL,
  `custlastname` varchar(30) default NULL,
  `custemail` varchar(30) default NULL,
  `custcountry` varchar(30) default NULL,
  `custzip` varchar(30) default NULL,
  `custstate` varchar(30) default NULL,
  `custcity` varchar(30) default NULL,
  `custaddress` varchar(30) default NULL,
  `custphone` varchar(30) default NULL,
  `custfax` int(11) default NULL,
  PRIMARY KEY  (`orderid`)
);

This table contain the information about the product.ADS_TO_REPLACE_4

Product table

CREATE TABLE `product` (
  `productid` int(11) NOT NULL auto_increment,
  `categoryname` varchar(50) default NULL,
  `productname` varchar(60) default NULL,
  `productprice` varchar(40) default NULL,
  `listprice` varchar(20) default NULL,
  `userImage` varchar(60) default NULL,
  `quantity` varchar(11) default NULL,
  `description` varchar(80) default NULL,
  `briefdisc` text,
  PRIMARY KEY  (`productid`)
);

This table contains the information about the users, their login id, address, email, phone and may important data too.

User tableADS_TO_REPLACE_5

CREATE TABLE `user` (
  `id` int(11) NOT NULL auto_increment,
  `userid` varchar(30) NOT NULL,
  `password` varchar(30) default NULL,
  `firstname` varchar(30) default NULL,
  `lastname` varchar(30) default NULL,
  `email` varchar(30) default NULL,
  `country` varchar(30) default NULL,
  `zip` varchar(30) default NULL,
  `state` varchar(30) default NULL,
  `city` varchar(30) default NULL,
  `address` varchar(30) default NULL,
  `phone` varchar(30) default NULL,
  `fax` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `userid` (`userid`)
)

You can also download the database backup given below and paste them to create the database of the shopping cart.

Download Database Backup

Related Tags for Database Design :