1 2 Previous Next 16 Replies Latest reply on Jan 28, 2017 10:21 AM by user28177

    Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?

    user28177

      Hello,

       

      My name is Paulo, and I work for a Video and Film Post-Production company.

       

      I'm working on a FileMaker solution that will help us better visualize our business.

       

      Our Main table is the JOBS table.

       

      Here we have two different kinds of Jobs: Advertising and Entertainment. They are both very similar, but there are a couple of fields that are different, for example, an Advertising Job needs an advertising agency and a creative diretor. So instead of creating two tables, one for JOBS_Advertising and another for JOBS_Entertainment, I've added all necessary fields in a single JOB table, and then created two different JOBS layouts to reflect the specific fields of each Job Kind (Advertising or Entertainment).

       

      First question: Is this the best approach, or I should've create TWO jobs table (one for advertising and another for entertainment)?

       

      Next:

       

      JOBS are basically two kinds:

       

      - Advertising

      - Entertainment

       

       

       

      If the Job is an Advertising Job, it needs a client and an ad agency linked to it.

      If the Job is an Entertainment Job, it needs either a production company and distribution company (if it's a feature film), or a production company and network (if it's a TV Show).

       

       

      Advertising Agencies, Advertising Clients, Production Companies, Networks and distribution companies are in the end all COMPANIES and share most if not all fields of a COMPANY Table.

       

       

       

       

      Should they share the same table and be connected via different Table Occurrences or should they belong to different tables?

      (In the attached image, I've started connecting them as separeted Tables, but then I started to question myself, since I'll have to add two extra tables, one for networks and another for distribution companies.)

       

      Final question:

       

      I want to create a CONTACTS table.

       

      A CONTACT can be a either a COMPANY or a PERSON.

       

      Each COMPANY can be the following:

       

      • ADVERTISING AGENCIES: We're usually hired by advertising agencies, who are hired by advertising clients (those that own the brands that will be advertised).
      • ADVERTISING CLIENTS: AD clients are the ones who owned the brands and hired advertising agency. Apple, Unilever or McDonalds can't be considered advertising clients. (Ex. Citibank, Apple, Unilever, McDonald's, etc.)
      • ENTERTAINMENT CLIENTS: These clients are usually production companies. They are the ones that usually hire us for entertainment work.
      • DISTRIBUITION COMPANIES: They are the ones that sell a feature film to a cinema. Ex. Warner Bros. Universal Studios, etc.
      • NETWORKS: TV Shows are broadcasted or streamed. Ex. Netflix, HBO, etc.
      • SUPPLIERS: Any company that sell us gear.

      In Advertising: Every JOB must have a AD CLIENT and a AD AGENCYIn Entertainment: IF the JOB is a feature film, it must have a production company AND a distribution company. IF the job is a TV Show, it must have a production company AND a network.

      The reason I'm breaking this down is because I believe it directly interfere in the way the relationships have to be connected.

       

      PERSON:

      • any contact that is worth keeping
      • An employee (or former) (has to be linked with an HR table, in the future!!)
      • A person that is a contact at a company (advertising agencies, advertising clients, entertainment clients, distribution companies, etc)
      • A person that is a contact at a supplier

       

      Please note that a PERSON record does not necessarily has to be connected to a COMPANY record.

      Please note that a PERSON who is an employee needs to be linked to the HR table so it must have an Employee ID.

       

      The reason I'm breaking this down is because I believe it directly interfere in the way the relationships have to be connected.

       

      So, in sum, here's what I need to know:

       

      What's the best way of connecting these relationships?

       

      Thank you!!

       

      Best,

       

      Paulo

       

      Screen Shot 2017-01-16 at 4.27.47 PM.png

        • 1. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
          erolst

          As to your first question:

           

          You can abstract the entire description into: "every job has a number of companies that are labeled as having different roles (and could be culled from different pools), but are basically all just companies"

           

          So I'd suggest

           

          Job --< JobCompany >-- Company

           

          where JobCompany has an additional attribute companyRole - unless every company could only have a single role in all your projects that could thus be always read reliably from the Company table. This is something I'd never rely on ...

           

          By the same token, I'd take out all those function-specific fields out of the Jobs table and use

           

          Job --< JobPerson >-- Person

           

          where JobCompany has an additional attribute personRole - colorist, VFX coordinator, AD, DoP etc.

          2 of 2 people found this helpful
          • 2. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
            user28177

            Hi Erolst,

             

            Thanks so much for getting back to me.

             

            Indeed all could have been explained in a single sentence. However, as you'me mentioned, a job has only one company role. There are no advertising agencies in a job, nor two clients. And most jobs won't have any supplier.

             

            I think the most fundamental question is, since, for example, all ad jobs need an ad agency, should the best approach be to duplicate the table so I can have an ad agency table, and ID and a Client table, and ID? Or maybe create two serial number fields and separate these two company roles using different table occurrences?

             

            Regarding the PERSON / PEOPLE records, I've already set up as you've suggested. I have a ROLES table that acts as a join table between jobs and people. That works well. my question here is whats the best approach to create a centralized CONTACTS table, that links people, workers, companies, clients, ad agencies, suppliers, etc.

             

            Thanks again!!

             

            Best,

             

            Paulo

            • 3. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
              keywords

              The first thing I would do is put all the Company records into the same table. If you look at your Ad Agency and Client tables as depicted you see essentially the same fields in each, just preceded with different first part of the name to distinguish which table they are from. The entities in this table are all the same thing—companies. You can then link them through join tables to the Job table to distinguish the nature of the join—agency or client. This structure will make it much easier for you in many ways, notably (1) one set of data to manage instead of two; (2) dealing with the possible future occasion when a company you have previously dealt with on one project as an agency is later involved with you as a client.

              • 4. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
                jfletch

                I always only have one table for all companies and then I typically set a field for company type that has a finite list of options (e.g., Vendor, Customer, Associate, etc.). Then I create a set of calculated number field booleans for each status:

                 

                isVendor = [ companyType = "Vendor" ]

                isCustomer = [ companyType = "Customer" ]

                etc.

                 

                I use those fields in queries and relationships to filter lists and company sets to match the company type I want. "DRY" methodologies (Don't Repeat Yourself) mean that you do not have more than one table with the same fields in it.

                 

                I even have a solution for a non-profit where all companies and people are in the same table call "Entities," with statuses of types of "Company" and "Individual."

                 

                In a similar manner, I typically put all phone numbers, email addresses and web addresses in one table called "Contact_Methods."

                 

                A clear understanding of relational principles will make your solutions simpler and more flexible.

                1 of 1 people found this helpful
                • 5. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
                  user28177

                  Dear jfletch,

                   

                  Thanks for the feedback. Indeed, I'm reading all I can about relational principles in order to fully understand it.

                   

                  I had the feeling that the less repeated fields the better, and your approach looks like the best way to handle this.

                   

                  I only have one question. Since an Advertising Agency and a Client are both Companies and each advertising jobs require both a Client_ID and an Agency_ID, but they belong to the same table, should I create a field for Agency and another for Client and link them to different TOs of Companies, or should I create a join table and use a portal in the layout?

                   

                  Because if all company types are sharing the same table, I cannot have two Company_IDs on the same layout, unless I use a join table and a portal.

                   

                  Thanks again!

                   

                  Best,

                   

                  Paulo

                  • 6. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
                    jfletch

                    This is a typical issue for most advertising projects: there can be several entities involved: an advertiser, an ad agency, a creative agency, and a media outlet, for example. I would just put a foreign key for each in the jobs table: ID_advertiser (or ID_client), ID_adAgency, ID_creative, and id_outlet. Have each be a Pop-up Menu that displays the company name, but stores the company ID. You can even filter the value lists of the Pop-ups so that they only show you companies that have a particular status. [1]

                     

                    [Please use UUIDs here, as it will make your life a lot easier with a setup like this.]

                     

                    Then your interface will allow you to enter as many or as few of the related companies as you need.

                     

                    [1] See next post.

                    • 7. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
                      richardsrussell

                      This is just a comment on nomenclature. I too regularly use a table that includes both humans and organizations. I call it "Beings". I use the "Sex" field with values F (female), M (male), O (organization), and X (unknown) to differentiate among them. I reserve the name "Contacts" for the table that contains records of my client (the database user) having contacted a being via e-mail, phone, meeting, etc.

                       

                      I second the above advice to consolidate all your companies into a single table, and I further recommend putting your people into that same table. Humans and orgs share many characteristics — names, addresses, contact methods, date entered, notes, identifying codes, time zones, etc. — that you won't have to duplicate the way you would if they were in separate tables. This will simplify and speed up your development process.

                      2 of 2 people found this helpful
                      • 8. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
                        jfletch

                        [In light of the need to store multiple IDs for the various related companies, I would recommend a variation on the approach mentioned in my first post.]

                         

                        Instead of having ONE type field and various boolean calculations parsing its contents, I would forego the type field altogether. Instead, use simple checkbox fields that allow a company to have multiple types. Then a company can be listed as a customer and an ad agency AND a creative agency, or whatever combination you need.

                         

                        With the multiple foreign keys in your job record you can choose one company more than once for each of the individual relationships. The advantage to having multiple typing in the customer record is so you can have the same company show up in multiple value lists.

                         

                        It would be a simple matter to just have all companies show up in all the value lists for all the company relationships to the job record. It is more user friendly, however, to filter the value lists based on company type, making them shorter. Having multiple type fields on the company allows you to have the company show up in more than one "short" value list, as needed.

                         

                        [If you have too many companies for even the "short" value lists, then going to some kind of "picker portal" might be in order.]

                         

                        You can still have the type checkboxes be "boolean" by having each checkbox's value list be "1" and then hiding the text by reducing the border of the field on the layout. That way checking a box will give you the same boolean as originally suggested with the calculation: 1 or 0 (or null), and its suggested relational functionality.

                         

                        I know this is a lot to process if you were not already thinking along these lines, so feel free to ask more questions as you need.

                        1 of 1 people found this helpful
                        • 9. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
                          jfletch

                          Richard, yes, and as I mentioned I HAVE put people and companies in the same table before, but you add a bit of complexity to the relationship when you try to see who's related to who. It involves a bit of table self-joinery and is not for beginners or the faint of heart.

                          • 10. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
                            PeterDoern

                            This is very much like the Party Model popularized by Len Silverston. The Data Model Resource Book Volumes 1 to 3 are fun reading if data modelling is your bag, but not for the faint of heart.

                            1 of 1 people found this helpful
                            • 11. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
                              user28177

                              Peter,

                               

                              Thanks so much for the tip! Already bought volume 1 and it will help me a lot in planning my solution!

                               

                              Best,

                               

                              Paulo

                              • 12. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
                                user28177

                                Did just that! Much simpler indeed! Thanks a lot for the help!

                                 

                                Best,

                                 

                                Paulo

                                • 13. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
                                  user28177

                                  Dear Peter,

                                   

                                  I liked the book a lot. Thanks again for the tip. I want to try to follow the book relationship models as much as I can. So I believe it makes sense to have people and companies sharing the same ENTITY table.

                                   

                                  With that said, I have a simple question: Is there anyway to create filtered value lists based on entity type? The only way I was able to flter the types is by creating a PopOver Button and placing a portal inside with a filter. But what I really wanted was to have fields in my layout for the different types: Agency, Client, Contact, etc. Since they all share the same table (Entity), but have different types (Company or Person) and subtypes (Agency, Client, Supplier, etc).

                                   

                                  I'am able to filter a value list, only by making another table occurrence and selecting the type in a global field first, then the next field will only show me the subtypes, that I can do. What I wanted was the field already filtered and not having to select the type first, so the agency field for example would only show Entities that Mach the subtype Agency.

                                   

                                  Does that make sense?

                                   

                                  Thanks a lot!

                                   

                                  Best,

                                   

                                  Paulo

                                  • 14. Re: Should I use Individual Tables or Table Ocurrences to Clients and Suppliers, whom are both "Companies"?
                                    user28177

                                    Jfletch,

                                     

                                    Thank you for the tip. Reading your post again, I went that route.

                                     

                                    Is there anyway to create filtered value lists based on Company type? The only way I was able to flter the types is by creating a PopOver Button and placing a portal inside with a filter (like you said). But what I really wanted was to have fields in my layout for the different types: Agency, Client, Contact, etc. Since they all share the same table (Companies), but have different types (Agency, Client, Supplier, etc).

                                     

                                    I'am able to filter a value list, only by making another table occurrence and selecting the type in a global field first, then the next field will only show me the subtypes, that I can do. What I wanted was the field already filtered and not having to select the type first, so the agency field for example would only show Entities that Mach the type Agency.

                                     

                                    Thanks again!

                                     

                                    Best,

                                     

                                    Paulo

                                    1 2 Previous Next