* of hier informatie mist
* ieder ander commentaar behalve op het data-type van de kolommen
Ik heb dit grafisch ontworpen en de SQL code laten genereren.
answers.answer kan een nederlands of frans antwoord bevatten
answers.direction moet als boolean functioneren of de vraag als frans->nederlands of nederlands->frans is gesteld
het is niet nodig om het later uit te breiden naar andere talen
CREATE TABLE IF NOT EXISTS `mydb`.`dictionaries` (
`id` INT NOT NULL ,
`name` TEXT NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mydb`.`phrases` (
`id` INT NOT NULL ,
`dictionaryId` INT NULL ,
`french` TEXT NULL ,
`dutch` TEXT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_phrases_dictionaries_idx` (`dictionaryId` ASC) ,
CONSTRAINT `fk_phrases_dictionaries`
FOREIGN KEY (`dictionaryId` )
REFERENCES `mydb`.`dictionaries` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mydb`.`practiseSessions` (
`id` INT NOT NULL ,
`dictionaryId` INT NULL ,
`started` DATETIME NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_practiseSessions_dictionaries1_idx` (`dictionaryId` ASC) ,
CONSTRAINT `fk_practiseSessions_dictionaries1`
FOREIGN KEY (`dictionaryId` )
REFERENCES `mydb`.`dictionaries` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mydb`.`answers` (
`id` INT NOT NULL AUTO_INCREMENT ,
`phraseId` INT NOT NULL ,
`direction` INT NOT NULL ,
`incorrect` INT NOT NULL ,
`answer` TEXT NOT NULL ,
`caseInsensitive` INT NULL ,
`ignorePunctuation` INT NULL ,
`practiseSessionId` INT NULL ,
PRIMARY KEY (`id`) ,
INDEX `fk_answers_phrases1_idx` (`phraseId` ASC) ,
INDEX `fk_answers_practiseSessions1_idx` (`practiseSessionId` ASC) ,
CONSTRAINT `fk_answers_phrases1`
FOREIGN KEY (`phraseId` )
REFERENCES `mydb`.`phrases` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_answers_practiseSessions1`
FOREIGN KEY (`practiseSessionId` )
REFERENCES `mydb`.`practiseSessions` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;Originele uitvoer: http://pastebin.com/VT7asMBY
