CODEWITHSUNDEEP
database-designrelationshipoption-typejoin
balteo
balteo

Reputation: 24679

Database design and optionality in relationships of join tables

I am designing a database model and a question occurred to me: what's the point of specifying whether the join-table side of a relationship is optional bearing in mind it has no effect on the generated DDL?

For instance take the two different diagrams below:

Diagram with mandatory relationship between A and A_to_B_join on the A_to_B_join side: mandatory

Diagram with optional relationship between A and A_to_B_join on the A_to_B_join side:

optional

I noticed they both stictly generate the same DDL even though they are different!

Generated DDL:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`A`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`A` (
  `A_ID` INT NOT NULL ,
  PRIMARY KEY (`A_ID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`B`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`B` (
  `B_ID` INT NOT NULL ,
  PRIMARY KEY (`B_ID`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`A_to_B_join`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`A_to_B_join` (
  `A_ID` INT NOT NULL ,
  `B_ID` INT NOT NULL ,
  PRIMARY KEY (`A_ID`, `B_ID`) ,
  INDEX `fk_A_to_B_join_B1` (`B_ID` ASC) ,
  INDEX `fk_A_to_B_join_A` (`A_ID` ASC) ,
  CONSTRAINT `fk_A_to_B_join_A`
    FOREIGN KEY (`A_ID` )
    REFERENCES `mydb`.`A` (`A_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_A_to_B_join_B1`
    FOREIGN KEY (`B_ID` )
    REFERENCES `mydb`.`B` (`B_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

What's the point then of mentioning whether the side is optional or mandatory? Should I bother? Is the fact that the DDL is the same specific to MySQL?

Regards,

Upvotes: 0

Views: 1259

Answers (1)

p.marino
p.marino

Reputation: 6252

It could very well be MySQL-specific.

Some other product (Oracle? Postgres? ...) could generate slightly different DDLs, and the difference could be used by the query optimizer to decide how to translate queries that include the Join.

Upvotes: 2

Related Questions