Re: Lookup Table with Optional Fields? Null primary key for blanks?
I'm new enough that I'm not sure what you're looking for, but here's some background:
The goal behind this to create a generic CRM that could scale to be cross-industry and have millions or billions of rows in the individual or entity tables and 5-50x as many in the lookup tables. The goal is to distinguish the concept of an entity from an individual and to identify all/any relationships between them.
Relationship types are extremely diverse, like: Customer, Employee, Sister, Agent, Self, CRM Admin, CEO, User, Salesman
I don't know SELECT statements very well, but the general idea would be:
Identify all relationships that belong to a specific individual that has a required entity relationship: Given that an Jim (individual) is employed (relationship) by his Widgetcorp (entity), identify all other individuals by Widgetcorp.
This is a "Forward Engineer" script from MySQL Workbench
-- -----------------------------------------------------
-- Table `crm`.`entity`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm`.`entity` (
`identity` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NULL ,
`dba` VARCHAR(45) NULL ,
`desc` VARCHAR(45) NULL ,
PRIMARY KEY (`identity`) ,
UNIQUE INDEX `id_UNIQUE` (`identity` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
-- -----------------------------------------------------
-- Table `crm`.`individual`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm`.`individual` (
`idindividual` INT NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`idindividual`) ,
UNIQUE INDEX `id_UNIQUE` (`idindividual` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
-- -----------------------------------------------------
-- Table `crm`.`relationship_type`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm`.`relationship_type` (
`idrelationship_type` INT NOT NULL ,
`desc` VARCHAR(45) NULL ,
PRIMARY KEY (`idrelationship_type`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `crm`.`individual_individual_relationship`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm`.`individual_individual_relationship` (
`individual1` INT NULL ,
`individual2` INT NULL ,
`relationship` INT NOT NULL ,
PRIMARY KEY (`individual1`, `relationship`, `individual2`) ,
INDEX `fk_idrelationship_type` (`relationship` ASC) ,
INDEX `fk_idindividual1` (`individual1` ASC) ,
INDEX `fk_idindividual2` (`individual2` ASC) ,
CONSTRAINT `fk_idindividual1`
FOREIGN KEY (`individual1` )
REFERENCES `crm`.`individual` (`idindividual` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_idrelationship_type`
FOREIGN KEY (`relationship` )
REFERENCES `crm`.`relationship_type` (`idrelationship_type` )
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT `fk_idindividual2`
FOREIGN KEY (`individual2` )
REFERENCES `crm`.`individual` (`idindividual` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
-- -----------------------------------------------------
-- Table `crm`.`entity_individual_relationship`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm`.`entity_individual_relationship` (
`entity1` INT NOT NULL ,
`individual1` INT NOT NULL ,
`relationship` INT NOT NULL ,
PRIMARY KEY (`entity1`, `individual1`, `relationship`) ,
INDEX `fk_idindividual` (`individual1` ASC) ,
INDEX `fk_identity` (`entity1` ASC) ,
INDEX `fk_idrelationship_type` (`relationship` ASC) ,
CONSTRAINT `fk_identity`
FOREIGN KEY (`entity1` )
REFERENCES `crm`.`entity` (`identity` )
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT `fk_idindividual`
FOREIGN KEY (`individual1` )
REFERENCES `crm`.`individual` (`idindividual` )
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT `fk_idrelationship_type`
FOREIGN KEY (`relationship` )
REFERENCES `crm`.`relationship_type` (`idrelationship_type` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
-- -----------------------------------------------------
-- Table `crm`.`entity_entity_relationship`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm`.`entity_entity_relationship` (
`entity1` INT NOT NULL ,
`entity2` INT NOT NULL ,
`relationship` INT NOT NULL ,
PRIMARY KEY (`entity1`, `relationship`, `entity2`) ,
INDEX `fk_idrelationship_type` (`relationship` ASC) ,
INDEX `fk_identity1` (`entity1` ASC) ,
INDEX `fk_identity2` (`entity2` ASC) ,
CONSTRAINT `fk_identity1`
FOREIGN KEY (`entity1` )
REFERENCES `crm`.`entity` (`identity` )
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT `fk_idrelationship_type`
FOREIGN KEY (`relationship` )
REFERENCES `crm`.`relationship_type` (`idrelationship_type` )
ON DELETE NO ACTION
ON UPDATE CASCADE,
CONSTRAINT `fk_identity2`
FOREIGN KEY (`entity2` )
REFERENCES `crm`.`entity` (`identity` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;