PJ
PB>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.
PJ>Forgive my naiveté, but how do you do that?
You write application code to implement the equivalent of ON DELETE
CASCADE | SET NULL | ... when a parent row is deleted.
> For normalisation, however, you need the A-B-AB setup no matter what
> engine you use.
PJ>What is simpler?
Using InnoDB.
Workbench gave you a model with two book_authors tables? I suggest
asking about it in the Workbench forum.
PB
-----
PJ wrote:
> 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;
> ------------------------------------------------------------------------
>
>
> Internal Virus Database is out of date.
> Checked by AVG - http://www.avg.com
> Version: 8.0.176 / Virus Database: 270.10.12/1909 - Release Date: 1/22/2009 7:08 AM
>
>
