title: Database-schema Design for e-commerce website
body: <p>I am making an E-Commerce website and using MySQL for storing data.</p>
  
  <p>I have added the Schema diagram below . Please let me know the possible flaw in Design or other problem may occurred in it.</p>
  
  <p>The Products for which i'm designing have lot for variation in terms of its Finish,Dimensions and have their unique property.</p>
  
  <p>here is what i'm trying to achieve:</p>
  
  <ul>
  <li>Product A can have 1 or more Variations .(eg: 1.AA( matt finish) , AB(Wood Finish) , AC(Oil Finish)).</li>
  <li>Each variation can have 0 or multiple dimensions such as 30x40,40x60 and so on .(eg: AA can come in 30x40 and 40x60)</li>
  <li>each product has their unique property . (eg: product A can have Thickness , product B can have Class)</li>
  <li>The SKU and Price of a Product depends upon all its different Dimension,Finish and their unique properties.</li>
  </ul>
  
  <p>for the unique property I could have used EAV but instead went with this approach.</p>
  
  <p><a href="https://i.stack.imgur.com/CRdym.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/CRdym.png" alt="The SQL Schema Design"></a>
  let me just explain the tables here:</p>
  
  <ul>
  <li><strong>Brands,Category and Collections</strong> are exactly what they sounds.</li>
  <li><strong>base_relation_table</strong> is the relation between above 3 tables , all possible combinations.</li>
  <li><strong>Product</strong> (contains all the products eg: Product A,Product B) and referencing <strong>ID</strong> of <strong>base_relation_table</strong> to know which combination it belongs to. It also referencing the <strong>Pattern</strong> which contain its look like (stone,woodgrain and so on).</li>
  <li><strong>product_option_relation</strong> is a relation for unique property (eg Product A ID, Thickness ID)</li>
  <li><strong>option_table</strong> contain only the name (thickness,Class,printing Technology and so on)</li>
  <li><strong>option_value</strong> contains all the values of the option_tables (eg: 0.7,0.9,Professional,Beginners etc)</li>
  <li><strong>product_variant</strong> contains all variation of products (eg: AA,AB,AC,BA,BB etc)</li>
  <li><strong>color,finish</strong> are 1:n with all product variant .(eg: AA matt red,AB shine white)</li>
  <li><strong>Dimensions</strong> contains n:m relation with <strong>product_variant</strong></li>
  <li><strong>variant_values</strong> is the combinations of all property,all its variations and unique properties.</li>
  </ul>
  
  <p>As I mention earlier SKU and Price changes with its unique and its variation properties so I have added it here . Coz here I'm getting all the combinations.</p>
  
  <p>here are the schema code :</p>
  
  <pre><code>SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
  
  -- -----------------------------------------------------
  -- Schema Catelogue
  -- -----------------------------------------------------
  
  -- -----------------------------------------------------
  -- Schema Catelogue
  -- -----------------------------------------------------
  CREATE SCHEMA IF NOT EXISTS `Catelogue` DEFAULT CHARACTER SET utf8 ;
  USE `Catelogue` ;
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`brands`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`brands` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`brands` (
    `ID` VARCHAR(45) NOT NULL,
    `b_name` VARCHAR(45) NULL,
    `thumbnails` VARCHAR(45) NULL,
    PRIMARY KEY (`ID`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`categorys`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`categorys` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`categorys` (
    `ID` VARCHAR(45) NOT NULL,
    `c_name` VARCHAR(45) NULL,
    `thumbnails` VARCHAR(45) NULL,
    PRIMARY KEY (`ID`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`collections`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`collections` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`collections` (
    `ID` VARCHAR(45) NOT NULL,
    `co_name` VARCHAR(45) NULL,
    `thumbnails` VARCHAR(45) NULL,
    PRIMARY KEY (`ID`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`base_Relation_table`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`base_Relation_table` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`base_Relation_table` (
    `ID` INT NOT NULL,
    `Brands_ID` VARCHAR(45) NOT NULL,
    `Categorys_ID` VARCHAR(45) NOT NULL,
    `Collections_ID` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`ID`),
    INDEX `fk_base_Relation_table_Brands_idx` (`Brands_ID` ASC) VISIBLE,
    INDEX `fk_base_Relation_table_Categorys1_idx` (`Categorys_ID` ASC) VISIBLE,
    INDEX `fk_base_Relation_table_Collections1_idx` (`Collections_ID` ASC) VISIBLE,
    CONSTRAINT `fk_base_Relation_table_Brands`
      FOREIGN KEY (`Brands_ID`)
      REFERENCES `Catelogue`.`brands` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_base_Relation_table_Categorys1`
      FOREIGN KEY (`Categorys_ID`)
      REFERENCES `Catelogue`.`categorys` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_base_Relation_table_Collections1`
      FOREIGN KEY (`Collections_ID`)
      REFERENCES `Catelogue`.`collections` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`pattern`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`pattern` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`pattern` (
    `ID` VARCHAR(45) NOT NULL,
    `option_name` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`ID`),
    UNIQUE INDEX `values_UNIQUE` (`option_name` ASC) VISIBLE)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`product`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`product` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`product` (
    `ID` INT NOT NULL,
    `p_name` VARCHAR(45) NULL,
    `Description` VARCHAR(45) NULL,
    `base_Relation_table_ID` INT NOT NULL,
    `pattern_ID` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`ID`),
    INDEX `fk_Product_base_Relation_table1_idx` (`base_Relation_table_ID` ASC) VISIBLE,
    UNIQUE INDEX `name_UNIQUE` (`p_name` ASC) VISIBLE,
    INDEX `fk_Product_pattern1_idx` (`pattern_ID` ASC) VISIBLE,
    CONSTRAINT `fk_Product_base_Relation_table1`
      FOREIGN KEY (`base_Relation_table_ID`)
      REFERENCES `Catelogue`.`base_Relation_table` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_Product_pattern1`
      FOREIGN KEY (`pattern_ID`)
      REFERENCES `Catelogue`.`pattern` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`colors`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`colors` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`colors` (
    `ID` INT NOT NULL,
    `color_name` VARCHAR(45) NULL,
    PRIMARY KEY (`ID`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`option_table`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`option_table` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`option_table` (
    `ID` INT NOT NULL,
    `option_name` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`ID`),
    UNIQUE INDEX `values_UNIQUE` (`option_name` ASC) VISIBLE)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`option_values`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`option_values` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`option_values` (
    `ID` INT NOT NULL,
    `Option_ID` INT NOT NULL,
    `value_name` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`ID`, `Option_ID`),
    UNIQUE INDEX `values_UNIQUE` (`value_name` ASC) VISIBLE,
    CONSTRAINT `fk_Option_values_Options1`
      FOREIGN KEY (`Option_ID`)
      REFERENCES `Catelogue`.`option_table` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`finish`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`finish` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`finish` (
    `ID` INT NOT NULL,
    `finish_name` VARCHAR(45) NULL,
    PRIMARY KEY (`ID`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`product_variant`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`product_variant` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`product_variant` (
    `Variant_ID` INT NOT NULL,
    `Product_ID` INT NOT NULL,
    `Finish_ID` INT NOT NULL,
    `Colors_ID` INT NOT NULL,
    `metadata` VARCHAR(45) NULL,
    `thumbnail` VARCHAR(45) NOT NULL DEFAULT '\" \"',
    INDEX `fk_ProductDetails_Finish1_idx` (`Finish_ID` ASC) VISIBLE,
    INDEX `fk_ProductDetails_Colors1_idx` (`Colors_ID` ASC) VISIBLE,
    INDEX `fk_Product_Variant_Product1_idx` (`Product_ID` ASC) VISIBLE,
    PRIMARY KEY (`Variant_ID`, `Product_ID`),
    CONSTRAINT `fk_ProductDetails_Finish1`
      FOREIGN KEY (`Finish_ID`)
      REFERENCES `Catelogue`.`finish` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_ProductDetails_Colors1`
      FOREIGN KEY (`Colors_ID`)
      REFERENCES `Catelogue`.`colors` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_Product_Variant_Product1`
      FOREIGN KEY (`Product_ID`)
      REFERENCES `Catelogue`.`product` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`product_option_relation`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`product_option_relation` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`product_option_relation` (
    `Product_ID` INT NOT NULL,
    `Option_ID` INT NOT NULL,
    INDEX `fk_Product_Option_Product1_idx` (`Product_ID` ASC) VISIBLE,
    INDEX `fk_Product_Option_Options1_idx` (`Option_ID` ASC) VISIBLE,
    PRIMARY KEY (`Product_ID`, `Option_ID`),
    CONSTRAINT `fk_Product_Option_Product1`
      FOREIGN KEY (`Product_ID`)
      REFERENCES `Catelogue`.`product` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_Product_Option_Options1`
      FOREIGN KEY (`Option_ID`)
      REFERENCES `Catelogue`.`option_table` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`dimensions`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`dimensions` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`dimensions` (
    `ID` INT NOT NULL,
    `dimensions_value` VARCHAR(45) NULL,
    PRIMARY KEY (`ID`))
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`dimensions_has_product_variant`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`dimensions_has_product_variant` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`dimensions_has_product_variant` (
    `Dimensions_ID` INT NOT NULL,
    `Product_ID` INT NOT NULL,
    `Variant_ID` INT NOT NULL,
    PRIMARY KEY (`Dimensions_ID`, `Product_ID`, `Variant_ID`),
    INDEX `fk_Dimensions_has_Product_Variant_Product_Variant1_idx` (`Product_ID` ASC, `Variant_ID` ASC) VISIBLE,
    CONSTRAINT `fk_Dimensions_has_Product_Variant_Dimensions1`
      FOREIGN KEY (`Dimensions_ID`)
      REFERENCES `Catelogue`.`dimensions` (`ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_Dimensions_has_Product_Variant_Product_Variant1`
      FOREIGN KEY (`Product_ID` , `Variant_ID`)
      REFERENCES `Catelogue`.`product_variant` (`Product_ID` , `Variant_ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Catelogue`.`variant_value`
  -- -----------------------------------------------------
  DROP TABLE IF EXISTS `Catelogue`.`variant_value` ;
  
  CREATE TABLE IF NOT EXISTS `Catelogue`.`variant_value` (
    `Product_ID` INT NOT NULL,
    `Option_ID` INT NOT NULL,
    `Value_ID` INT NOT NULL,
    `Dimensions_ID` INT NOT NULL,
    `Variant_ID` INT NOT NULL,
    `price` VARCHAR(45) NOT NULL,
    `SKU` VARCHAR(45) NULL,
    PRIMARY KEY (`Product_ID`, `Option_ID`, `Value_ID`, `Dimensions_ID`, `Variant_ID`),
    INDEX `fk3_option_values_idx` (`Value_ID` ASC, `Option_ID` ASC) VISIBLE,
    INDEX `fk2_product_options_idx` (`Product_ID` ASC, `Option_ID` ASC) VISIBLE,
    INDEX `fk_variant_value_dimensions_has_product_variant1_idx` (`Dimensions_ID` ASC, `Variant_ID` ASC, `Product_ID` ASC) VISIBLE,
    CONSTRAINT `fk2_product_options`
      FOREIGN KEY (`Product_ID` , `Option_ID`)
      REFERENCES `Catelogue`.`product_option_relation` (`Product_ID` , `Option_ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk3_option_values`
      FOREIGN KEY (`Value_ID` , `Option_ID`)
      REFERENCES `Catelogue`.`option_values` (`ID` , `Option_ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `fk_variant_value_dimensions_has_product_variant1`
      FOREIGN KEY (`Dimensions_ID` , `Variant_ID` , `Product_ID`)
      REFERENCES `Catelogue`.`dimensions_has_product_variant` (`Dimensions_ID` , `Variant_ID` , `Product_ID`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  SET SQL_MODE=@OLD_SQL_MODE;
  SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  </code></pre>
  
  <p>is there anything i'm missing ? will it be scale ? is the design looks good?</p>