Creating Database Schema for an Online Shopping Application

This assignment solution presents a comprehensive database schema designed for an online shopping system. It includes the SQL code to create various tables such as 'admin,' 'category,' 'orders,' 'ordertrackhistory,' 'productreviews,' 'products,' 'subcategory,' and 'users.' Each table's structure, including column names, data types, and constraints, is defined. The schema also includes sample data for some tables, showcasing how information is stored and managed. The SQL script sets the SQL mode and begins a transaction to ensure data integrity. The schema covers essential aspects of an e-commerce platform, from product details and user information to order tracking and reviews. This database design is intended to efficiently manage and organize the data required for an online shopping application. The solution is ideal for students looking to understand database design principles and implement them in a practical context. This is a valuable resource for students to learn about database design and implementation.

1
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!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 utf8mb4 */;
--
-- Database: `shopping`
--
-- --------------------------------------------------------
--
-- Table structure for table `admin`
--
CREATE TABLE `admin` (
`id` int(11) NOT NULL,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`creationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

2
`updationDate` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `admin`
--
INSERT INTO `admin` (`id`, `username`, `password`, `creationDate`, `updationDate`) VALUES
(1, 'admin', 'f925916e2754e5e03f75dd58a5733251', '2017-01-18 16:21:18', '21-06-2019 08:27:55
PM');
-- --------------------------------------------------------
--
-- Table structure for table `category`
--
CREATE TABLE `category` (
`id` int(11) NOT NULL,
`categoryName` varchar(255) DEFAULT NULL,
`categoryDescription` longtext,
`creationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updationDate` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--

3
-- Dumping data for table `category`
--
INSERT INTO `category` (`id`, `categoryName`, `categoryDescription`, `creationDate`,
`updationDate`) VALUES
(3, 'T-shirts', 'Testtees', '2018-01-10 19:17:37', '30-01-2020 12:22:24 AM'),
(4, 'Full T-shirt', 'Electron like', '2017-01-08 19:19:32', '');
-- --------------------------------------------------------
--
-- Table structure for table `orders`
--
CREATE TABLE `orders` (
`id` int(11) NOT NULL,
`userId` int(11) DEFAULT NULL,
`productId` varchar(255) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`orderDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`paymentMethod` varchar(50) DEFAULT NULL,
`orderStatus` varchar(55) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `orders`

4
--
INSERT INTO `orders` (`id`, `userId`, `productId`, `quantity`, `orderDate`, `paymentMethod`,
`orderStatus`) VALUES
(9, 4, '21', 1, '2019-08-15 05:12:16', 'Internet Banking', NULL);
-- --------------------------------------------------------
--
-- Table structure for table `ordertrackhistory`
--
CREATE TABLE `ordertrackhistory` (
`id` int(11) NOT NULL,
`orderId` int(11) DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
`remark` mediumtext,
`postingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `productreviews`
--

5
CREATE TABLE `productreviews` (
`id` int(11) NOT NULL,
`productId` int(11) DEFAULT NULL,
`quality` int(11) DEFAULT NULL,
`price` int(11) DEFAULT NULL,
`value` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`summary` varchar(255) DEFAULT NULL,
`review` longtext,
`reviewDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- --------------------------------------------------------
--
-- Table structure for table `products`
--
CREATE TABLE `products` (
`id` int(11) NOT NULL,
`category` int(11) NOT NULL,
`subCategory` int(11) DEFAULT NULL,
`productName` varchar(255) DEFAULT NULL,
`productCompany` varchar(255) DEFAULT NULL,
`productPrice` int(11) DEFAULT NULL,
`productPriceBeforeDiscount` int(11) DEFAULT NULL,

6
`productDescription` longtext,
`productImage1` varchar(255) DEFAULT NULL,
`productImage2` varchar(255) DEFAULT NULL,
`productImage3` varchar(255) DEFAULT NULL,
`shippingCharge` int(11) DEFAULT NULL,
`productAvailability` varchar(255) DEFAULT NULL,
`postingDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updationDate` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `products`
--
INSERT INTO `products` (`id`, `category`, `subCategory`, `productName`, `productCompany`,
`productPrice`, `productPriceBeforeDiscount`, `productDescription`, `productImage1`,
`productImage2`, `productImage3`, `shippingCharge`, `productAvailability`, `postingDate`,
`updationDate`) VALUES
(21, 3, 1, 'my tees', 'levis', 1800, 1500, 'good', 'tshirt.jpg', 'tshirt.jpg', '', 80, 'In Stock', '2019-08-15
05:08:51', NULL);
-- --------------------------------------------------------
--
-- Table structure for table `subcategory`
--

7
CREATE TABLE `subcategory` (
`id` int(11) NOT NULL,
`categoryid` int(11) DEFAULT NULL,
`subcategory` varchar(255) DEFAULT NULL,
`creationDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`updationDate` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `subcategory`
--
INSERT INTO `subcategory` (`id`, `categoryid`, `subcategory`, `creationDate`, `updationDate`)
VALUES
(1, 3, 'Logo T-shirt', '2019-08-26 20:30:00', '2019-08-21');
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,

8
`email` varchar(255) DEFAULT NULL,
`contactno` bigint(11) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`shippingAddress` longtext,
`shippingState` varchar(255) DEFAULT NULL,
`shippingCity` varchar(255) DEFAULT NULL,
`shippingPincode` int(11) DEFAULT NULL,
`billingAddress` longtext,
`billingState` varchar(255) DEFAULT NULL,
`billingCity` varchar(255) DEFAULT NULL,
`billingPincode` int(11) DEFAULT NULL,
`regDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updationDate` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `name`, `email`, `contactno`, `password`, `shippingAddress`,
`shippingState`, `shippingCity`, `shippingPincode`, `billingAddress`, `billingState`, `billingCity`,
`billingPincode`, `regDate`, `updationDate`) VALUES
(4, 'MAx', '[e-mail]', 784512, '827ccb0eea8a706c4c34a16891f84e7b', NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, '2019-08-15 05:11:45', NULL);
--

9
-- Indexes for dumped tables
--
--
-- Indexes for table `admin`
--
ALTER TABLE `admin`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `category`
--
ALTER TABLE `category`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `ordertrackhistory`
--
ALTER TABLE `ordertrackhistory`
ADD PRIMARY KEY (`id`);

10
--
-- Indexes for table `productreviews`
--
ALTER TABLE `productreviews`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `products`
--
ALTER TABLE `products`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `subcategory`
--
ALTER TABLE `subcategory`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
--

11
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `admin`
--
ALTER TABLE `admin`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `category`
--
ALTER TABLE `category`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
--
-- AUTO_INCREMENT for table `ordertrackhistory`
--
ALTER TABLE `ordertrackhistory`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `productreviews`
--

12
ALTER TABLE `productreviews`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;
--
-- AUTO_INCREMENT for table `subcategory`
--
ALTER TABLE `subcategory`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;COMMIT;
/*!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 */;