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