Database-schema design for e-commerce website

Question

I am making an E-Commerce website using MySQL. Please let me know the possible flaw in design or other problems. The products have variation in finish and dimensions.

• Product A can have 1 or more variations (1.AA matt finish, AB wood finish, AC oil finish).
• Each variation can have 0 or more dimensions (AA can come in 30x40 and 40x60).
• Each product has their unique property (product A can have thickness, product B can have class).
• The SKU and price of a product depends on all its different properties.

For the unique property I could have used EAV but instead went with this :

• Brands, Category and Collections are what they say.
• base_relation_table is the relation between above 3 tables, all possible combinations.
• Product contains all products (Product A, Product B) and reference ID of base_relation_table to which combination it belongs. It also references Pattern which contain its look (stone, woodgrain etc.).
• product_option_relation is a relation for unique property (Product A ID, Thickness ID).
• option_table contains only the name (thickness, class, printing technology etc.).
• option_value contains all values of the option_tables (0.7, 0.9, Professional, Beginners, etc.).
• product_variant contains all variations of products (AA, AB, AC, BA, BB, etc).
• color,finish are 1:n with product variants (AA matt red, AB shine white).
• Dimensions contains n:m relation with product_variant.
• variant_values is the combination of all properties, all its variations and unique properties.

Schema :

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;

Is there anything I'm missing? Will it scale? Does it look good?

Answer 1

It is "over-normalized". For example, look at colors. You are replacing a 3-byte 'red' with a 4-byte integer. No space savings. (Ok, "magenta" takes more than 4 bytes, but not much.)

The other purpose for normalizing is to make it easier to change the spelling for "red" throughout the dataset. I don't think that will happen.

So... Have columns for the "common" or "important" or "commonly searched on" attributes.

For other attributes, toss them into a JSON string and make it a column called, say, other_attributes.

Though your schema is more complex than the common EAV, here is a discussion of why EAV is poor and what to do about it: http://mysql.rjweb.org/doc.php/eav

As for dimensions, think about what you will do with the value or list of values. I suspect you don't do anything with "30x40,40x60" other than display that list on the screen. It is inherently un-searchable. So the only practical thing is to put that string inside the JSON that I suggested.