List:General Discussion« Previous MessageNext Message »
From:PJ Date:February 10 2009 5:44pm
Subject:Re: db setup - correction
View as plain text  
Peter Brawley wrote:
> PJ
>
> >But what about foreign keys? Don't I need that to find the relationships
> >between the books, the authors and the categories? After all, isn't this
> >a relational db? If so, I can't use the default engine (MyISAM) which
> >does not support FK. So, if I have to use foreign keys, I have to change
> >to INNODB, right?
>
> Engine choice is another issue. InnoDB properly isolates FK
> enforcement in the database. The MyISAM tradeoff (for speed) is that
> you have to implement FK logic in code.
Forgive my naiveté,  but how do you do that?
> For normalisation, however, you need the A-B-AB setup no matter what
> engine you use.
>
What is simpler?
BTW, I have set up an EER diagram using MySQL Workbench... and exported
this sql script...
does it make sense?
Strange that it created two instances to create the table book_author???
Maybe a glitch in Workbench....

-- -----------------------------------------------------
-- Table `language`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `language` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `language` VARCHAR(7) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `books`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `books` (
  `id` SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR(148) NULL ,
  `sub_title` VARCHAR(90) NULL ,
  `descr` TINYTEXT NULL ,
  `comment` TEXT NULL ,
  `bk_cover` VARCHAR(32) NULL ,
  `publish_date` YEAR NULL ,
  `ISBN` BIGINT(13) NULL ,
  `language_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_books_language` (`language_id` ASC) ,
  CONSTRAINT `fk_books_language`
    FOREIGN KEY (`language_id` )
    REFERENCES `biblane`.`language` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `authors`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `authors` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `first_name` VARCHAR(32) NULL ,
  `last_name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `categories`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `category` VARCHAR(70) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = MyISAM;


-- -----------------------------------------------------
-- Table `sellers`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `sellers` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `seller_link` VARCHAR(128) NULL ,
  `seller_img` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `publishers`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `publishers` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `publisher` VARCHAR(72) NOT NULL ,
  `pub_link`  NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `book_author`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `book_author` (
  `books_id` SMALLINT(4) UNSIGNED NOT NULL ,
  `author_id` INT UNSIGNED NOT NULL ,
  `list_order` TINYINT(1) NULL ,
  PRIMARY KEY (`books_id`, `author_id`) ,
  INDEX `fk_book_author_books` (`books_id` ASC) ,
  INDEX `fk_book_author_authors` (`author_id` ASC) ,
  CONSTRAINT `fk_book_author_books`
    FOREIGN KEY (`books_id` )
    REFERENCES `biblane`.`books` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_book_author_authors`
    FOREIGN KEY (`author_id` )
    REFERENCES `biblane`.`authors` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `book_author`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `book_author` (
  `books_id` SMALLINT(4) UNSIGNED NOT NULL ,
  `author_id` INT UNSIGNED NOT NULL ,
  `list_order` TINYINT(1) NULL ,
  PRIMARY KEY (`books_id`, `author_id`) ,
  INDEX `fk_book_author_books` (`books_id` ASC) ,
  INDEX `fk_book_author_authors` (`author_id` ASC) ,
  CONSTRAINT `fk_book_author_books`
    FOREIGN KEY (`books_id` )
    REFERENCES `biblane`.`books` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_book_author_authors`
    FOREIGN KEY (`author_id` )
    REFERENCES `biblane`.`authors` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `book_publisher`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `book_publisher` (
  `books_id` SMALLINT(4) UNSIGNED NOT NULL ,
  `publishers_id` INT NOT NULL ,
  PRIMARY KEY (`books_id`, `publishers_id`) ,
  INDEX `fk_books_has_publishers_books` (`books_id` ASC) ,
  INDEX `fk_books_has_publishers_publishers` (`publishers_id` ASC) ,
  CONSTRAINT `fk_books_has_publishers_books`
    FOREIGN KEY (`books_id` )
    REFERENCES `biblane`.`books` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_books_has_publishers_publishers`
    FOREIGN KEY (`publishers_id` )
    REFERENCES `biblane`.`publishers` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `book_categories`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `book_categories` (
  `books_id` SMALLINT(4) UNSIGNED NOT NULL ,
  `categories_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`books_id`, `categories_id`) ,
  INDEX `fk_books_has_categories_books` (`books_id` ASC) ,
  INDEX `fk_books_has_categories_categories` (`categories_id` ASC) ,
  CONSTRAINT `fk_books_has_categories_books`
    FOREIGN KEY (`books_id` )
    REFERENCES `biblane`.`books` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_books_has_categories_categories`
    FOREIGN KEY (`categories_id` )
    REFERENCES `biblane`.`categories` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `book_seller`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `book_seller` (
  `seller_id` INT NOT NULL ,
  `books_id` SMALLINT(4) UNSIGNED NOT NULL ,
  PRIMARY KEY (`seller_id`, `books_id`) ,
  INDEX `fk_seller_has_books_seller` (`seller_id` ASC) ,
  INDEX `fk_seller_has_books_books` (`books_id` ASC) ,
  CONSTRAINT `fk_seller_has_books_seller`
    FOREIGN KEY (`seller_id` )
    REFERENCES `biblane`.`seller` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_seller_has_books_books`
    FOREIGN KEY (`books_id` )
    REFERENCES `biblane`.`books` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB; 
Thread
db setupPJ9 Feb
  • Re: db setupOlaf Stein9 Feb
  • Re: db setup - correctionOlaf Stein9 Feb
    • Re: db setup - correctionPJ9 Feb
      • Re: db setup - correctionPeter Brawley9 Feb
        • Re: db setup - correctionOlaf Stein9 Feb
        • Re: db setup - correctionPJ9 Feb
          • Re: db setup - correctionOlaf Stein9 Feb
          • Re: db setup - correctionPeter Brawley9 Feb
            • Re: db setup - correctionPJ10 Feb
              • Re: db setup - correctionPeter Brawley10 Feb
                • Re: db setup - correctionPJ10 Feb
                  • Re: db setup - correctionPeter Brawley10 Feb
                  • Re: db setup - correctionddevaudreuil10 Feb
                    • Size limitation of user variable?Bryan Cantwell10 Feb
                      • Re: Size limitation of user variable?Baron Schwartz10 Feb
                        • Re: Size limitation of user variable?Johan De Meersman11 Feb
                          • Re: Size limitation of user variable?Baron Schwartz11 Feb
Re: db setup - correctionPJ10 Feb