MySQL Forums
Forum List  »  Newbie

Re: Lookup Table with Optional Fields? Null primary key for blanks?
Posted by: Daniel Wingard
Date: September 13, 2012 02:15PM

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;

Options: ReplyQuote


Subject
Written By
Posted
Re: Lookup Table with Optional Fields? Null primary key for blanks?
September 13, 2012 02:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.