Question

Database design of cothing 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 wish-list / watchlist is needed, the schema can be simply extended. Enjoy. SQL and methodology.

Answer 1

Requirements:

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

Install XAMPP or WAMP and go to your localhost and then phpmyadmin to create database.

In users table ->

user_ID INT(11)

firstname VARCHAR(255)

middlename VARCHAR(255)

lastname VARCHAR(255)

email VARCHAR(255)

password VARCHAR(255)

address VARCHAR(255)

phone VARCHAR(255)

pincode INT(11)

type VARCHAR(255)

created_at INT(11)

In products table ->

product_ID INT(11)

name VARCHAR(255)

description VARCHAR(255)

category_ID INT(11)

image VARCHAR(255)

quantity VARCHAR(255)

brand VARCHAR(255)

model VARCHAR(255)

configuration VARCHAR(255)

price VARCHAR(255)

featured INT(11)

created_at INT(11)

In category table ->

id INT(11)

name VARCHAR(255)

details VARCHAR(255)

In contact table ->

contact_ID INT(11)

firstname VARCHAR(255)

middlename VARCHAR(255)

lastname VARCHAR(255)

email VARCHAR(255)

message VARCHAR(255)

created_at INT(11)

In orders table ->

order_ID INT(11)

user_ID INT(11)

shipping_address VARCHAR(255)

shipping_date INT(11)

shipping_status VARCHAR(255)

created_at INT(11)

In order details table ->

order_details_id INT(11)

order_id INT(11)

product_id INT(11)

quantity INT(11)

In sales table ->

sales_id INT(11)

order_id INT(11)

sales_amount INT(11)

created_at INT(11)

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`