组依赖SQL设计 [英] Group dependency SQL design

问题描述

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

I have an entity which has NOT NULL requirements based on the group it belongs to. For instance...

• 教堂共有三种类型:佛教，穆斯林和基督教.
• 所有教堂都有一些共同的必需属性，但是，每种类型的教堂都有其他必需的属性.
• 所有人都有一些共同的必需属性，但是，根据他们所属的教堂类型，他们还具有其他必需属性.
• 人们必须属于一个教堂，并且只有一个教堂，但是，只要满足上述规则，就可以将其教堂更改为任何一种宗教.他们的人的类型"是基于他们所属的教堂的类型.

具有所需属性的实体应如何基于该实体所属的组进行建模?还是根据我的情况，应该如何对教会和人民进行建模?

How should entities who's required properties are based on the group which the entity belongs to be modeled? Or given my scenario, how should churches and people be modeled?

这是我目前正在做的事情，但似乎不正确.例如，可以在一个人成为违反规则的佛教徒，穆斯林或基督教徒之前添加一个人.另外，一个人或一个教会可以是不止一种规则的一种以上的类型.

This is currently what I am doing, but it does not seem right. For instance, a person can be added before they become a Buddhist, Muslim, or Christian which breaks the rules. Also, a person or church can be more than one type which also breaks the rules.

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

推荐答案

一种惯用语强制声明不相交的子类型:

One idiom declaratively enforcing disjoint subtypes:

• 将类型鉴别符/标签列 religion_type 添加到父表和子表
• 将超级键 UNIQUE NOT NULL(id，religion_type)添加到父表
• 向子表添加 FOREIGN (超级) KEY(id，religion_type)，并引用父项
• 添加约束 CHECK(ligure_type =' 宗教 ')或值为的常量计算列宗教 到子表

• add type discriminator/tag column religion_type to parent and child tables
• add superkey UNIQUE NOT NULL (id, religion_type)to parent tables
• add FOREIGN (super) KEY (id, religion_type) to child tables, referencing parents
• add constraint CHECK( religion_type = 'religion' ) or constant computed column with value religion to child tables

这仍然不能强制每个父母都是孩子.来自此答案:

This still doesn't enforce that every parent is a child. From this answer:

需要触发器来合理限制SQL数据库.人们使用习语来获得声明约束.

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

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

Just find the straightforward predicate for each relevant application relationship and give it a table. Here, that's parent and child tables. The constraints follow from the predicates and possible situtations. Declare them to prevent impossible updates. Whenever values in some columns must appear in other columns we declare a FK. You don't have to think about a special case for subtyped entity ids. Certain ids will end up in certain tables because certain things are true of them. It is ultimately their satisfying different predicates that makes things of different "types", rather than vice versa.

这篇关于组依赖SQL设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！