I'm having trouble understanding how to set up these relationships.

I have the following tables:
Companies, contacts, notes.

There can be many contacts for one company.
There can be many notes for each contact.
There can be many notes for each company.

When I pull up a company record I need to see all the notes for that company including those which are for all contacts from that company. So even though a note is attached to a contacts record it should also automatically be attached to the company that contact belongs to as well.

What's the best way to set that up?

Thanks!

I would think you would have a notes field for company and a separate notes field for contact, being that a contact will have notes related to company 'A' and notes related to company 'B'.

If you looked at a contact you would want to see all his notes for all companies

If you look at a company you want to see Company notes, & only the related contact notes.

Hi Steve,

There's only one company per contact so I want all notes in one table. Here's what I'm trying to achieve:

Add one more table for clarification called "staff" where one staff member has many companies.

A client rep (staff) goes into the database and makes a note about one of our clients (a company). The note is record in the notes table and has the staff members name in a field and is linked to the companies table. They then go into the contacts of that company and make a note about one of the contacts.

I want to be able to go into the record of a company and see all notes about that company whether they are about the company itself or about any of the contacts of that company. Each should contain the name of the staff member who created the note. 

Does that make more sense? I know there's an easy solution to this and just want to make sure I set it up correctly.

Thanks!

You can either store the company ID and the Contact ID in the notes table (as I do most of the time) or you can show the companies' contacts' notes going two levels deep.

The name of the person should not be stored, rather the account that created should be stored by using Created By. And also use a Created Date field.


Sent from my iPhone using Tapatalk