mysql - 组依赖SQL设计

Question

我有一个实体，根据其所属的组，该实体具有 NOT NULL 要求。例如...

• 教堂分为三种类型:佛教教堂、穆斯林教堂和基督教教堂。
• 所有教堂都有一些共同的必需属性，但是，每种类型的教堂都有额外的必需属性。
• 所有人都有一些共同的必需属性，但是，根据他们所属的教会类型，他们还有其他必需的属性。
• 人们必须属于一个且仅一个教会，但是，只要满足上述规则，就可以将其教会改为任何其他一种宗教。他们的“类型”取决于他们所属的教会类型。

如何根据实体所属的组对所需属性的实体进行建模？或者考虑到我的场景，教会和人们应该如何建模？

这就是我目前正在做的事情，但似乎不对。例如，可以在某人成为佛教徒、穆斯林或基督徒之前添加一个人，这违反了规则。此外，一个人或一个教会可以有多种类型，这也违反了规则。

-- MySQL Script generated by MySQL Workbench
-- 02/10/17 21:41:31
-- Model: New Model    Version: 1.0
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,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`churches`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`churches` (
  `idchurches` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `address` VARCHAR(45) NOT NULL,
  `members` INT NOT NULL,
  PRIMARY KEY (`idchurches`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`churches_buddhist`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`churches_buddhist` (
  `churches_idchurches` INT NOT NULL,
  `number_of_buddas_in_church` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`churches_idchurches`),
  CONSTRAINT `fk_churches_buddhist_churches`
    FOREIGN KEY (`churches_idchurches`)
    REFERENCES `mydb`.`churches` (`idchurches`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`churches_muslim`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`churches_muslim` (
  `churches_idchurches` INT NOT NULL,
  `savior` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`churches_idchurches`),
  CONSTRAINT `fk_churches_muslim_churches1`
    FOREIGN KEY (`churches_idchurches`)
    REFERENCES `mydb`.`churches` (`idchurches`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`churches_christian`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`churches_christian` (
  `churches_idchurches` INT NOT NULL,
  `savior` VARCHAR(45) NOT NULL,
  `number_of_crosses_in_church` INT NOT NULL,
  PRIMARY KEY (`churches_idchurches`),
  CONSTRAINT `fk_churches_christian_churches1`
    FOREIGN KEY (`churches_idchurches`)
    REFERENCES `mydb`.`churches` (`idchurches`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`people`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`people` (
  `idpeople` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `age` TINYINT NOT NULL,
  `race` VARCHAR(45) NOT NULL,
  `gender` VARCHAR(45) NOT NULL,
  `favoriteVegitable` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`idpeople`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`buddhists`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`buddhists` (
  `people_idpeople` INT NOT NULL,
  `WidthOfBelly` BIGINT NOT NULL,
  `LevelOfCconsciousness` INT NOT NULL,
  `churches_buddhist_churches_idchurches` INT NOT NULL,
  PRIMARY KEY (`people_idpeople`),
  INDEX `fk_buddhists_churches_buddhist1_idx` (`churches_buddhist_churches_idchurches` ASC),
  CONSTRAINT `fk_buddhists_people1`
    FOREIGN KEY (`people_idpeople`)
    REFERENCES `mydb`.`people` (`idpeople`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_buddhists_churches_buddhist1`
    FOREIGN KEY (`churches_buddhist_churches_idchurches`)
    REFERENCES `mydb`.`churches_buddhist` (`churches_idchurches`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`muslims`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`muslims` (
  `people_idpeople` INT NOT NULL,
  `DaysOffTakenForRamadan` INT NOT NULL,
  `favoriteMeat` VARCHAR(45) NOT NULL,
  `churches_muslim_churches_idchurches` INT NOT NULL,
  PRIMARY KEY (`people_idpeople`),
  INDEX `fk_muslims_churches_muslim1_idx` (`churches_muslim_churches_idchurches` ASC),
  CONSTRAINT `fk_muslims_people1`
    FOREIGN KEY (`people_idpeople`)
    REFERENCES `mydb`.`people` (`idpeople`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_muslims_churches_muslim1`
    FOREIGN KEY (`churches_muslim_churches_idchurches`)
    REFERENCES `mydb`.`churches_muslim` (`churches_idchurches`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`christians`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`christians` (
  `people_idpeople` INT NOT NULL,
  `ChristmasPresentsReceived` INT NOT NULL,
  `HolyMarysSaidPerDay` INT NOT NULL,
  `favoriteMeat` VARCHAR(45) NOT NULL,
  `FavoritePork` VARCHAR(45) NOT NULL,
  `churches_christian_churches_idchurches` INT NOT NULL,
  PRIMARY KEY (`people_idpeople`),
  INDEX `fk_christians_churches_christian1_idx` (`churches_christian_churches_idchurches` ASC),
  CONSTRAINT `fk_christians_people1`
    FOREIGN KEY (`people_idpeople`)
    REFERENCES `mydb`.`people` (`idpeople`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_christians_churches_christian1`
    FOREIGN KEY (`churches_christian_churches_idchurches`)
    REFERENCES `mydb`.`churches_christian` (`churches_idchurches`)
    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;

Answer 1

一个惯用法声明性地强制执行不相交的子类型:

• 向父表和子表添加类型鉴别器/标签列 religion_type
• 将 super 键UNIQUE NOT NULL (id,religion_type)添加到父表
• 将FOREIGN( super )KEY(id，religion_type)添加到子表，引用父表
• 添加约束 CHECK(religion_type = 'religion' ) 或值为 的常量计算列religion 到子表

这仍然不能强制每个 parent 都是 child 。来自 this answer :

One needs triggers to reasonably constrain SQL databases. One uses idioms to get what declarative constraints one can.

只需找到每个相关应用程序关系的简单谓词并给它一个表格即可。在这里，这是父表和子表。约束来自谓词和可能的情况。声明它们是为了防止不可能的更新。每当某些列中的值必须出现在其他列中时，我们就声明一个 FK。您不必考虑子类型实体 ID 的特殊情况。某些 id 最终会出现在某些表中，因为某些事情对它们来说是正确的。最终是它们满足不同的谓词，使得事物具有不同的“类型”，而不是相反。