Online Shopping System–Database Design

MySQL DBMS
Database design of Online Shopping System. Schema represents minimal information required to store information of a shop and products to sell. Cart information can be stored in session or if wishlist / watchlist is needed, the schema can be simply extended. Enjoy.

Entities:

users – shop admin, owner and customers
products – products to sell
categories – category of products. eg. laptops, phones etc
orders – customer placed a order
order_details – order always won’t have a single item. store details here.
sales – order is sold.
contact – when customers / visitors contact shop owner
smartshop
MySQL Create Script / phpMyAdmin dump:
— phpMyAdmin SQL Dump
— version 3.2.0.1
http://www.phpmyadmin.net

— Host: localhost
— Generation Time: Apr 03, 2013 at 07:51 AM
— Server version: 5.1.37
— PHP Version: 5.3.0
SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

— Database: `smartshop`

— ——————————————————–

— Table structure for table `category`

CREATE TABLE IF NOT EXISTS `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`details` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

— Dumping data for table `category`

INSERT INTO `category` (`id`, `name`, `details`) VALUES
(1, ‘laptops’, ‘laptops lorem ipsum’),
(2, ‘phones’, ‘phones details’),
(3, ‘tablets’, ‘tablets details’);
— ——————————————————–

— Table structure for table `contact`

CREATE TABLE IF NOT EXISTS `contact` (
`contact_id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(255) NOT NULL,
`middlename` varchar(255) DEFAULT NULL,
`lastname` varchar(255) NOT NULL,
`emai` varchar(255) NOT NULL,
`message` varchar(255) NOT NULL,
`created_at` int(11) NOT NULL,
PRIMARY KEY (`contact_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

— Dumping data for table `contact`

— ——————————————————–

— Table structure for table `orders`

CREATE TABLE IF NOT EXISTS `orders` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`shiping_address` varchar(255) DEFAULT NULL,
`shiping_date` int(11) DEFAULT NULL,
`shipping_status` varchar(255) DEFAULT NULL,
`created_at` int(11) NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

— Dumping data for table `orders`

— ——————————————————–

— Table structure for table `order_details`

CREATE TABLE IF NOT EXISTS `order_details` (
`order_details_id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
PRIMARY KEY (`order_details_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

— Dumping data for table `order_details`

— ——————————————————–

— Table structure for table `products`

CREATE TABLE IF NOT EXISTS `products` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` text NOT NULL,
`image` varchar(255) NOT NULL,
`category_id` int(11) NOT NULL,
`quantity` varchar(255) NOT NULL,
`brand` varchar(255) DEFAULT NULL,
`model` varchar(255) DEFAULT NULL,
`configuration` varchar(255) DEFAULT NULL,
`price` varchar(255) NOT NULL,
`featured` int(11) NOT NULL,
`created_at` int(11) NOT NULL,
PRIMARY KEY (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

— Dumping data for table `products`

— ——————————————————–

— Table structure for table `sales`

CREATE TABLE IF NOT EXISTS `sales` (
`sales_id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL,
`sales_amount` int(11) NOT NULL,
`created_at` int(11) NOT NULL,
PRIMARY KEY (`sales_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

— Dumping data for table `sales`

— ——————————————————–

— Table structure for table `users`

CREATE TABLE IF NOT EXISTS `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(255) NOT NULL,
`middlename` varchar(255) DEFAULT NULL,
`lastname` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
`type` varchar(255) NOT NULL,
`created_at` int(11) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

— Dumping data for table `users`

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

3 comments

Leave a Reply

Your email address will not be published. Required fields are marked *