1
\$\begingroup\$

I am trying to design multi purpose printshop database on which I will develop my basic store system. Beofre I tried to design it using EVL (Entity-Attribute-Value) aproach, however some users would pushe me in to other solutions like:

  • Single Table Inheritance
  • Class Table Inheritance
  • Concrete Table Inheritance

or even

  • Serialized LOB

And so I though I could give it a go.

Here I am trying to use a mixture of Single Table Inheritance and Class Table Inheritace design. It would be nice if somenone will take a look and sugest some fix and twirks to the design in general.

If you could take a pick at product hierarhy and linking product in to order_item. This will require some improvement fo sure.

DATABASE STRUCTURE REPRESENTATION:

enter image description here

DATABASE BUILD QUERY:

-- MySQL Script generated by MySQL Workbench
-- Thu Oct  5 04:15:05 2017
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mega_print_shop
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mega_print_shop
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mega_print_shop` DEFAULT CHARACTER SET utf8 ;
USE `mega_print_shop` ;

-- -----------------------------------------------------
-- Table `mega_print_shop`.`customer`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mega_print_shop`.`customer` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `surname` VARCHAR(45) NOT NULL,
  `login` VARCHAR(50) NOT NULL,
  `password` VARCHAR(45) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `address` VARCHAR(45) NOT NULL,
  `bloked` TINYINT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC),
  UNIQUE INDEX `login_UNIQUE` (`login` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mega_print_shop`.`product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mega_print_shop`.`product` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `in_stock` INT NOT NULL DEFAULT 1,
  `in_sale` TINYINT NOT NULL DEFAULT 1,
  `img` TEXT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mega_print_shop`.`produkt_print`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mega_print_shop`.`produkt_print` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(45) NOT NULL,
  `name` VARCHAR(250) NOT NULL,
  `finish` VARCHAR(250) NULL,
  `paper` VARCHAR(250) NULL,
  `color` VARCHAR(45) NULL,
  `quntity` VARCHAR(45) NULL,
  `price` DECIMAL(8,2) NOT NULL,
  `price_with_graphic_design` DECIMAL(8,2) NOT NULL,
  `in_sale` TINYINT NOT NULL DEFAULT 1,
  `graphic_design_service` TINYINT NOT NULL DEFAULT 0,
  `production_time` VARCHAR(45) NOT NULL DEFAULT '6 working days',
  `fk_product_type` INT NOT NULL,
  PRIMARY KEY (`id`, `fk_product_type`),
  INDEX `fk_produkt_print_product_type1_idx` (`fk_product_type` ASC),
  CONSTRAINT `fk_produkt_print_product_type1`
    FOREIGN KEY (`fk_product_type`)
    REFERENCES `mega_print_shop`.`product` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = ' ';


-- -----------------------------------------------------
-- Table `mega_print_shop`.`address`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mega_print_shop`.`address` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `country` VARCHAR(45) NULL,
  `city` VARCHAR(45) NOT NULL,
  `postal` VARCHAR(45) NOT NULL,
  `street_house` VARCHAR(255) NOT NULL,
  `customer_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_address_customer1_idx` (`customer_id` ASC),
  CONSTRAINT `fk_address_customer1`
    FOREIGN KEY (`customer_id`)
    REFERENCES `mega_print_shop`.`customer` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mega_print_shop`.`delivery`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mega_print_shop`.`delivery` (
  `name` VARCHAR(255) NOT NULL,
  `price` DECIMAL(8,2) NULL,
  `duration` VARCHAR(45) NULL,
  `tracking link` TEXT NULL,
  PRIMARY KEY (`name`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mega_print_shop`.`order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mega_print_shop`.`order` (
  `id` INT NOT NULL,
  `fk_customer` INT NOT NULL,
  `payed` TINYINT NULL,
  `payed_at` DATETIME NULL,
  `shiped_at` DATETIME NULL,
  `shiped` TINYINT NULL,
  `quantity` INT NULL,
  `total_price` VARCHAR(45) NULL,
  `created_at` VARCHAR(45) NULL,
  `fk_address` INT NOT NULL,
  `fk_delivery` VARCHAR(255) NOT NULL,
  `user_feedback` TEXT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_orde_customer1_idx` (`fk_customer` ASC),
  INDEX `fk_order_address1_idx` (`fk_address` ASC),
  INDEX `fk_order_delivery1_idx` (`fk_delivery` ASC),
  CONSTRAINT `fk_orde_customer1`
    FOREIGN KEY (`fk_customer`)
    REFERENCES `mega_print_shop`.`customer` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_order_address1`
    FOREIGN KEY (`fk_address`)
    REFERENCES `mega_print_shop`.`address` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_order_delivery1`
    FOREIGN KEY (`fk_delivery`)
    REFERENCES `mega_print_shop`.`delivery` (`name`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mega_print_shop`.`order_basket`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mega_print_shop`.`order_basket` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `fk_order` INT NOT NULL,
  PRIMARY KEY (`id`, `fk_order`),
  INDEX `fk_order_basket_order1_idx` (`fk_order` ASC),
  CONSTRAINT `fk_order_basket_order1`
    FOREIGN KEY (`fk_order`)
    REFERENCES `mega_print_shop`.`order` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mega_print_shop`.`order_item`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mega_print_shop`.`order_item` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `fk_order_basket` INT NOT NULL,
  `fk_product` INT NOT NULL,
  `user_project` TEXT NULL,
  PRIMARY KEY (`id`, `fk_product`),
  INDEX `fk_order_item_order_basket1_idx` (`fk_order_basket` ASC),
  INDEX `fk_order_item_product1_idx` (`fk_product` ASC),
  CONSTRAINT `fk_order_item_order_basket1`
    FOREIGN KEY (`fk_order_basket`)
    REFERENCES `mega_print_shop`.`order_basket` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_order_item_product1`
    FOREIGN KEY (`fk_product`)
    REFERENCES `mega_print_shop`.`product` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mega_print_shop`.`produkt_retail_and_services`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mega_print_shop`.`produkt_retail_and_services` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(250) NULL,
  `img` VARCHAR(255) NULL,
  `price` DECIMAL(8,2) NULL,
  `in_stock` INT NOT NULL DEFAULT 1,
  `in_sale` TINYINT NOT NULL DEFAULT 1,
  `fk_product_type` INT NOT NULL,
  PRIMARY KEY (`id`, `fk_product_type`),
  INDEX `fk_produkt_retail_and_services_product_type1_idx` (`fk_product_type` ASC),
  CONSTRAINT `fk_produkt_retail_and_services_product_type1`
    FOREIGN KEY (`fk_product_type`)
    REFERENCES `mega_print_shop`.`product` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = ' ';


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.