From: Peter Brawley
Date: February 10 2009 6:05pm
Subject: Re: db setup - correction
List-Archive: http://lists.mysql.com/mysql/216255
Message-Id: <4991C1D1.8040905@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------020701020601090602050702"

--------------020701020601090602050702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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
>
>   

--------------020701020601090602050702--