Expand all | Collapse all

Input on best approach

  • 1.  Input on best approach

    Posted 04-25-2019 18:59
    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?

  • 2.  RE: Input on best approach

    Posted 04-25-2019 20:48
    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.

  • 3.  RE: Input on best approach

    Posted 04-26-2019 02:45
    ... also in the diagram above we need to add

    Company < Contacts.