categories & sub categories with many to many relationship

Question

I am starting new project but can not under stand where to start from.I need database design which has categories and 4 level sub categories but the product can have more than one category. so i m confused how my database should be.Plz help me out. Thank you very much for giving me your time in advance.

Answer 1

product: holds the products with id
category: hold the category with id and name AND the parent category of the category
product_category: holds the cross-relation between one or many products and one or many categories...

If you use InnoDB on mysql or a good DBMS you should define the foreign-key constraints:

CREATE  TABLE IF NOT EXISTS `mydb`.`category` (
  `id` INT NOT NULL ,
  `name` VARCHAR(45) NULL ,
  `parent_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `parentCategory_idx` (`parent_id` ASC) ,
  CONSTRAINT `parentCategory`
    FOREIGN KEY (`parent_id` )
    REFERENCES `mydb`.`category` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;



CREATE  TABLE IF NOT EXISTS `mydb`.`product` (
  `id` INT NOT NULL ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `mydb`.`product_category` (
  `product_id` INT NOT NULL ,
  `category_id` INT NOT NULL ,
  PRIMARY KEY (`product_id`, `category_id`) ,
  INDEX `product_idx` (`product_id` ASC) ,
  INDEX `category_idx` (`category_id` ASC) ,
  CONSTRAINT `product`
    FOREIGN KEY (`product_id` )
    REFERENCES `mydb`.`product` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `category`
    FOREIGN KEY (`category_id` )
    REFERENCES `mydb`.`category` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;