#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,118
    Rep Power
    537

    Polymorphic tables based classification of entity


    I have the following three church entities and the following three people entities. A church may be of only one type, and the same is true for people. All fields under each are required. The ID of churches of any type will need to be joined to another common table, and also ID of people of any type will need to be joined to another common table (not shown).

    All people must belong to a single church type, and whether they are a Buddhist, Muslim, or Christian is strictly based on the church they belong to. Note that they may change their church to any other one of any religion provided that the above rules are met.

    How should I model this?

    buddhist_churches
    • name
    • address
    • members
    • number_of_buddas_in_church


    muslim_churches
    • name
    • address
    • members
    • savior


    christian_churches
    • name
    • address
    • members
    • savior
    • number_of_crosses_in_church


    buddhists_people
    • name
    • age
    • race
    • gender
    • favoriteVegitable
    • WidthOfBelly
    • LevelOfCconsciousness


    muslim_people
    • name
    • age
    • race
    • gender
    • favoriteVegitable
    • DaysOffTakenForRamadan
    • favoriteMeat


    christians_people
    • name
    • age
    • race
    • gender
    • favoriteVegitable
    • HolyMarysSaidPerDay
    • favoriteMeat
    • FavoritePork


    One option is the following, but it doesn't support all the rules during the time a given person type is added or changed, and doesn't ensure that a church may be of only one type, nor a person may be of only one type.

    How should this be modeled? Thanks!



    Code:
    -- MySQL Script generated by MySQL Workbench
    -- 02/11/17 06:23:23
    -- 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;
    Attached Images
    Last edited by NotionCommotion; February 11th, 2017 at 08:40 AM. Reason: ERD image wasn't being displayed
  2. #2
  3. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,118
    Rep Power
    2010
    Can a person be member of one or more churches at same time?
    You have the field "number_of_*_in_churches", does this tell how many is members? if yes, this should be retrieved by a SUM on member table in your SQL.

    I was thinking about just have one church table, adding a new field for religion type.
    Fields like favoriteMeat and FavoritePork could be renamed to favoriteFood.
    DaysOffTakenForRamadan could be renamed to something like DaysOffTakenForPilgrimage.

    In the case a person can only be assigned to one church, similar idea for the person table and a church id etc.

    My main idea is to have a design which does not require creation of several tables, in case a new religion should be added.

    Allowing people to be member of several churhces, but require they have same religion? I am not sure if this validation should/can be done at the database level.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,118
    Rep Power
    537
    Originally Posted by MrFujin
    Can a person be member of one or more churches at same time?
    You have the field "number_of_*_in_churches", does this tell how many is members? if yes, this should be retrieved by a SUM on member table in your SQL.

    I was thinking about just have one church table, adding a new field for religion type.
    Fields like favoriteMeat and FavoritePork could be renamed to favoriteFood.
    DaysOffTakenForRamadan could be renamed to something like DaysOffTakenForPilgrimage.

    In the case a person can only be assigned to one church, similar idea for the person table and a church id etc.

    My main idea is to have a design which does not require creation of several tables, in case a new religion should be added.

    Allowing people to be member of several churhces, but require they have same religion? I am not sure if this validation should/can be done at the database level.
    Nope, they can and must belong to a single church. And the various properties cannot be generalized, but need to be specific for the given church/person type except for the ones I listed common to all. Believe it or not, the churches/people thing was just an analogy for another application and I had a hard time thinking of properties for them. Churches are communication protocols and people are persistent connections on that protocols. I need a common PK for all persistent connections, and the required properties that define that persistent connection as well as the protocol are based on the given protocol. The analogy is very good, and I felt it would be easier to ask about churches and people.

    Yea, it seems like I shouldn't have the additional three tables for both churches and people, but I hit a roadblock when trying to do so without them.

    Thank you for your reply.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2015
    Posts
    50
    Rep Power
    2
    That's quite a complicated structure, and every time you would like to add representation to a new religion you have to both add tables and add a lot of code.

    Maybe a better structure would be something like:

    people have one place of worship
    places of worship have one religion

    Now you can describe people as "Christian" or "Muslim" by the religion their place of worship belongs to (has one through).

    Then you could have a sort of EAV model for place of worship and people to describe their status within their places of worship, and things that describe them as a religion.

    Of course, EAV has its downsides. The first being that for large data sets, it's slooooow.
    Last edited by oakleaf; February 25th, 2017 at 06:45 PM.

IMN logo majestic logo threadwatch logo seochat tools logo