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
