Best practise for polymorphic associations in MySQL

Question

I have a MySQL database with 3 tables holding the main classes of data:

 companies (company_id)
 persons (person_id, company_id) 
 loans (loan_id, company_id) 

Both a 'loan' and a 'person' belong to a company. A company can have loans and a company can have people (such as directors, employees etc.)

There are several scenarios where other data can belong to either a company, a person or a loan, such as 'notes'. For example a user can add a 'note' specific to either a company, a person or a loan.

There are other examples of this such as 'addresses' and 'phone numbers', which can belong to either a company or a person.

I obviously want to have a single table for data such as 'notes', 'addresses' and 'phone numbers', however I am struggling with the best way to link these to companies, people and loans.

Lets use 'notes' as our example here.

Is it better practice to:

1/ Have 3 extra tables linking 'notes' to companies people and loans:

notes(note_id)
persons_notes(person_id, note_id)
companies_notes(company_id, note_id)
loans_notes (loan_id, note_id)

2/ Have a single table 'entities' which has a primary key entity_id used to link all 3 tables:

entities(entity_id)
persons(person_id, entity_id)
companies(company_id, entity_id)
loans (loan_id, entity_id)
notes (note_id, entity_id)

The latter makes some sense in terms of logic, but does not feel right as persons and companies are a completely different class of data to loans. Somehow it feels dirty.

Notes and addresses will be in a one to many relationship with loans, persons and companies. That is, a company may have many addresses or notes. That is the reason I was thinking of the link tables.

A company can have a registered address, and unlimited trading addresses. A person can have a current address, and previous addresses (if at current address less than 3 years).

In this case, it is the company which owns the loan (business loan). The people belong to the company and are usually the company's directors. So, a company can have many persons and many loans.

• A company/person/loan can each have many notes
• A company/person can each have many addresses

Answer 1

This question (in various forms) crops up regularly on this forum.

It is called EAV (Entity Attribute Value). For good reason, it is called an antipattern. There are many reasons not to use this form of data model. They are outlined here, here and here (and links within + look at other posters' replies in the same threads).

Not everyone agrees that EAV is always a bad thing, just most of the time. Aaron Bertrand (a big hitter on this group) has written this post on the matter. My advice, however, is to stick with a conventional design and (probably) make life easier for yourself down the line.

Why not just have three main tables as you describe

• Person
• Company
• Loan

And two "subsidiary" tables

• Address
• Note

Then Person, Company and Loan can have Address_id and Note_id fields. No need for complicated linking tables (at least not yet :-) ). Or to start with, you could even include Note and Address in your main table (if there's ever going to be one Address and one Note (BLOB) field)?

Whatever you do, avoid EAV until you're an experienced data-modeller. p.s. welcome to the forum!

[EDIT]

Could you tell us which RDBMS you are using? It might have some bearing on my replies. The moderators here don't like long ping-pong type chats.

I "threw together" a diagramme and script which should get you started.

Any further problems/issues, let me know. Much of app design and data modelling is trial and error - you produce a data model, start programming against it and you find all sorts of ways in which your original ideas can't work. That's what prototyping is for. Give it a bash and then work around issues.

And the script from that diagramme.

CREATE TABLE IF NOT EXISTS `mydb`.`Person` (
  `Person_id` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NOT NULL,
  `Employed_by` INT NULL,
  PRIMARY KEY (`Person_id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `mydb`.`Loan` (
  `Loan_id` INT NOT NULL AUTO_INCREMENT,
  `Amount` DECIMAL NOT NULL,
  `Other_fields` VARCHAR(45) NULL,
  PRIMARY KEY (`Loan_id`))
ENGINE = InnoDB;





CREATE TABLE IF NOT EXISTS `mydb`.`Person_loan` (
  `Person_id` INT NOT NULL,
  `Loan_id` INT NOT NULL,
  INDEX (`Person_id` ASC),
  INDEX (`Loan_id` ASC),
  INDEX (`Person_id` ASC, `Loan_id` ASC),
  CONSTRAINT `Person_Loan_P_FK`
    FOREIGN KEY (`Person_id`)
    REFERENCES `mydb`.`Person` (`Person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Person_Loan_L_FK`
    FOREIGN KEY (`Loan_id`)
    REFERENCES `mydb`.`Loan` (`Loan_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;




CREATE TABLE IF NOT EXISTS `mydb`.`Company` (
  `Company_id` INT NOT NULL AUTO_INCREMENT,
  `Company_name` VARCHAR(45) NULL,
  PRIMARY KEY (`Company_id`))
ENGINE = InnoDB;





CREATE TABLE IF NOT EXISTS `mydb`.`Works_for` (
  `Person_id` INT NULL,
  `Company_id` INT NULL,
  INDEX `Works_For_P_FK_idx` (`Person_id` ASC),
  INDEX `Works_For_C_FK_idx` (`Company_id` ASC),
  CONSTRAINT `Works_For_P_FK`
    FOREIGN KEY (`Person_id`)
    REFERENCES `mydb`.`Person` (`Person_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Works_For_C_FK`
    FOREIGN KEY (`Company_id`)
    REFERENCES `mydb`.`Company` (`Company_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;




CREATE TABLE IF NOT EXISTS `mydb`.`Company_loan` (
  `Company_id` INT NOT NULL,
  `Loan_id` INT NOT NULL,
  INDEX `Company_loan_C_L_PK` (`Company_id` ASC, `Loan_id` ASC),
  INDEX `Company_Loan_L_FK_idx` (`Loan_id` ASC),
  CONSTRAINT `Company_Loan_C_FK`
    FOREIGN KEY (`Company_id`)
    REFERENCES `mydb`.`Company` (`Company_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Company_Loan_L_FK`
    FOREIGN KEY (`Loan_id`)
    REFERENCES `mydb`.`Loan` (`Loan_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;