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;
