title: sql database design for ecommerce
body: <p><strong>I am working on e-commerce website.
  I have 3 levels of categories</strong></p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `parent_categories` (
        `parent_cat_id` int(11) NOT NULL AUTO_INCREMENT,
        `parent_cat_name` varchar(200) NOT NULL,
        `created_on` datetime NOT NULL,
        `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        `status` int(11) NOT NULL COMMENT '1=0 active ',
        PRIMARY KEY (`parent_cat_id`)
      ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=49 ;
  
  
  
  CREATE TABLE IF NOT EXISTS `child_categories` (
    `child_cat_id` int(11) NOT NULL AUTO_INCREMENT,
    `child_cat_name` varchar(200) NOT NULL,
    `parent_cat_id` int(11) NOT NULL,
    `created_on` datetime NOT NULL,
    `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`child_cat_id`),
    KEY `fk_parent_cat_id` (`parent_cat_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=64 ;
  
  
  
  CREATE TABLE IF NOT EXISTS `sub_child_categories` (
    `sub_child_cat_id` int(11) NOT NULL AUTO_INCREMENT,
    `sub_child_cat_name` varchar(200) NOT NULL,
    `child_cat_id` int(11) NOT NULL,
    `created_on` datetime NOT NULL,
    `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`sub_child_cat_id`),
    KEY `fk_child_cat_id` (`child_cat_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;
  </code></pre>
  
  <p><strong>This is my products table</strong></p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `products` (
    `product_id` int(11) NOT NULL AUTO_INCREMENT,
    `parent_cat_id` int(11) NOT NULL,
    `child_cat_id` int(11) NOT NULL,
    `sub_child_cat_id` int(11) NOT NULL,
    `name` varchar(100) NOT NULL,
    `sku` varchar(100) NOT NULL,
    `brand` varchar(100) NOT NULL,
    `description` text NOT NULL,
    `discount` int(11) NOT NULL,
    `tax` int(11) NOT NULL,
    PRIMARY KEY (`product_id`),
    KEY `p_id` (`parent_cat_id`),
    KEY `ch_id` (`child_cat_id`),
    KEY `sub_ch_id` (`sub_child_cat_id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
  </code></pre>
  
  <p><strong>Now My doubt is , how to design table for storing product specifications based on categories</strong> </p>
  
  <p>For example Consider Two Categories:</p>
  
  <ol>
  <li>Fashion</li>
  <li>Food</li>
  </ol>
  
  <p>In Fashion </p>
  
  <ol>
  <li>clothing->Shirts  : <strong>color,material,size,sleeves,size chart</strong></li>
  <li>footware->Shoes    : <strong>color,material,size,size chart</strong></li>
  <li>ethenic->Shawl    : <strong>color,material,size</strong></li>
  <li>Body care->perfume  : <strong>price based on weight</strong></li>
  </ol>
  
  <p>In Food</p>
  
  <ol>
  <li>Sweet    : <strong>color,Price based on weight</strong></li>
  <li>pickels->veg : <strong>Price based on weight</strong></li>
  </ol>