Input on best approach

  • 0
  • 1
  • Question
  • Updated 4 weeks ago
  • In Progress
Background:
I want to create a CRM that flows well between the tables Companies, Contacts, Opportunities, and Activities.

When for example a new opportunity is created, I'd like to choose the associated company, and have it auto-populate the contact fields First Name, Last Name, Phone (direct), Phone (mobile), and Website.

So maybe I'm making this too complicated, but I was wondering if there's a good argument for choosing one vs. the other ways of structuring this as described below:

Option 1
Companies is Parent table, all other tables are children of this table.
Opportunities and Activities are also children of Contacts.

Option 2
Companies is Parent table. 
Contacts is child of Companies.
Opportunities and Activities are each children of Contacts, and any relevant Company details are pulled through from Companies to Contacts to Activities or Opportunities.

Option 2 seems to work well, but I can see that a potential challenge is if a Company has more than one contact, that it would not know which contact to pick from, so maybe Option 1 is best and then create a conditional dropdown filtering the Contact selection based on the chosen company?
Photo of Chris Gylseth

Chris Gylseth

  • 70 Points

Posted 4 weeks ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
Chris,
I think that you are missing out on seeing a many to many relationship which gives you t do not have feel that you need to sacrifice anything to get something else.  

Here is my suggestion.

One Company has many Contacts
One Company has many Opportunities.
One Opportunity has Many Opportunity Contacts
One Contact has Many Opportunity Contacts.

So it's like this with a middle table so you can select which Contact to assign to that particular Opportunity.  The join record is called an Opportunity Contact and often these join tables may not have any other field other than their lookup fields from their parents.

Company < Opportunities < Opportunity Contacts > Contacts

The dropdown for selecting a contact for an "Opportunity Contact" would be a Conditional Drop Down (CDD) so that it only offers up contacts for the same Related Company as the opportunity is connected to.






Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
... also in the diagram above we need to add

Company < Contacts.