5 Replies Latest reply on May 19, 2017 4:21 AM by mz5005

    Design question: Enterprises - companies


      We have a table of business-contacts with therefore many times the same companies. We have made that into a separate table with only unique company names.  The next step is Enterprises vs Company Names. (IBM is an enterprise, but has many subsidiaries with a slightly or totally different name).

      Q1: How do I best set that up (design wise)?


      An Enterprise is a company in itself and has to remain also in the CompaniesUnique table (because many of our contacts work at IBM sec).

      I could therefore in CompaniesUnique "check" IBM as part of Enterprise IBM , but I foresee problems later with using that this way.

      I could also set up a separate Enterprises table with IBM, HP etc, give them an enterprise-code and select the proper Enterprise code later in CompaniesUnique per record.


      Q2: if I choose the" Enterprises-table way" will I not get problems with IBM and others appearing in both Enterprises and CompaniesUnique later on?


      Q3: most of the companies we know are (of course) SMBE, but could still be considered to be an "enterprise of themselves". We are not a data entry company, nor is our purpose to "design the most beautiful system". We like to keep things simple unless there is a good reason not to. My preference therefore is an Enterprise table with only the really large companies and in the CompaniesUnique the Enterprise field left to "ZZZ" (and an Enterprise with a name "ZZZ")

      Any reasons not to do it this way?


      Any advice to sweating amateur highly appreciated :-)

        • 1. Re: Design question: Enterprises - companies
          We have a table of business-contacts with therefore many times the same companies.

          OK, do I get it right: You enter the company data into fields on the individual person - and therefore you end up with the same company many times?


          You are doing it the same way as FileMaker is doing in this table from the starter solutions. It is a very very bad idea.

          The blue fields should definitely not have been in this table.


          Have a look at this structure, the illustration below.

          The field and key naming is not following our conventions, but FileMakers, but that's OK.


          This way you will have the company once and as many contacts connected to each company as you want.



          • 2. Re: Design question: Enterprises - companies

            I wish I could count the times on one hand where I (mistakenly) put address information into a "Customer" (or other) table only to realize the "Customer" had multiple addresses!


            You make is a great point.

            • 3. Re: Design question: Enterprises - companies

              You are right, in many cases addresses and other contract specific attributes could be put into a 3rd table, but here I wanted to keep the normalisation explanation rather simple.

              • 4. Re: Design question: Enterprises - companies

                Right. Understood. But, when I read your post it hit home. Good reinforcement.

                • 5. Re: Design question: Enterprises - companies

                  thanks carsten, fmpdude. i fully understand your point. however, our starting point is people (business contacts=BC) and not companies. these BC are exported from LinkedIN - one file per (our) employee.

                  this export module is rudimentary and has no way of steering what you export, so everything is imported into a separate part of the system. every new import replaces all previous info. the connection to the rest of the system (with our data) is made via the email address.

                  in short, what i am talking about is how to make a best design from the (bad) starting point of having an end-result of one big file with business contacts, with many multiples.

                  our system already does not import "double people", but still, of course many BC still work at the same company and/or enterprise.