6 Replies Latest reply on Dec 7, 2012 12:24 PM by philmodjunk

    People and Businesses in Different Entities



      People and Businesses in Different Entities


           Hello All thanks for the help.  It's been a long time since I created a relational database and I'm having trouble conceptualizing the EA for this new one.  

           Im creating a database to help me manage my business (Disaster Restoration, Water and Smoke Damage) and we will deal with People and Businesses.  The People can be Entities such as Referrers, Clients, Vendors, Subcontractors, Insurance Adjusters, and Employees.  Each Person may or may not be attached to a Company that we need to track.  The Companies can also be Entities such as Referral Offices, Clients, Insurance Companies, Adjusting Companies, Vendors, Subcontractors and they will have employees that we will need to keep track of as well.

           My EA shows the Entities as their functions and I can build the database this way but then I have no way to track all the relationships one person or business will have with us (A Person who is a Referrer or a Company who is a Referral Office can also be a Client and/or a Vendor and/or a Subcontractor)  If I add Person and Company to my EA it isn't "clean" and I worry that if I do this then It will associate incorrect information to a job file.  (Perform work for a person who works at a Company.  I dont want to associate the Company to the Job Automatically if the job was at the person's house)

           I'm sure that there is an easy way to do this But I can't find it in the documentation.  I'm Thinking that it is similar to a many to many relationship with a join table.  

           Does anyone know how to make this work?  Thanks,  Walker



        • 1. Re: People and Businesses in Different Entities

               Each "entity" in an Entity Relationship diagram need not refer to a different FileMaker Table, but could represent different occurrences of the same table in the FileMaker Relationships tab.

               Your entire list would seem to have a set of fields in common: Name, Address and other contact info.

               I'd set up a table of contacts where you have one record for each individual, whether they be a referral, a vendor, Employee, SubContractor... or some combination of all of the above.

               You can the, for just one example, set up a table of referrals where a record in this table use a ContactID field to refer to the name and contact information of the contact making the referral and a second ID field can link to the contact being referred.

          • 2. Re: People and Businesses in Different Entities

                 Lets See if I understand...

                 Create a People Table that holds all Fields specific to that person (Name, Home Address, Home Telephone, Credit Card Number, email address, etc.)

                 Create a Company Table that holds all Fields specific to that company (Name, Company Address, Company Telephone, website address, etc.)

                 Link People and Companies Together using Contact ID's so that I can know who works where and send letters to them or know their company telephone number using a portal

                 Then either:

                 Use Join Tables by:

                 Create Join Tables for Referral Office, Insurance Company, and Adjusting Company that only link to Companies in the Company Table

                 Create Join Tables for Employee, Referrer, and Insurance Adjuster that link only to People.

                 Create Join Tables for Referrer,  Client, Vendor, and Subcontractor that link to either People or to Companies 

                 (I'm having trouble picturing how this would work)


                 Create links from the Job Entity to individually include Client, Insurance Company, Insurance Adjuster(s), Vendor(s), Subcontractor(s), etc

                 (I'm unsure how to create fields that would link to either People or Companies) 




            • 3. Re: People and Businesses in Different Entities

                   Both Companies and People can be "contacts". Note the similar contact fields that you listed for each.

              • 4. Re: People and Businesses in Different Entities

                     Do I use Join Tables or Do I use Links to create relationships?


                     With both Individuals and Companies merged into one Contacts Entity the number of fields would be very unwieldy correct?  Imagine individuals home address, company physical mailing and shipping address, credit cards for both company and individuals.

                     I cant think how I would create a relationship from one contact to another in one Contact List (ex. Employee / Employer Relationship)

                     If I wanted to enter a Client that would either be an induvidual or a company the fields would be different (First and Last Name vs Company Name)

                • 5. Re: People and Businesses in Different Entities

                       Just found Self Joining Relationships which helps clear up the Contact Relationship Issue.

                       How Can I enter type specific data for either a company or an individual on the same data entry screen?

                  • 6. Re: People and Businesses in Different Entities

                         If you use the same table for both, this should function for you without any major issues, but I would only put contact information in the contacts table. If there is additional data to be recorded for companies, I'd add a companies table and link it to contacts via contactID.

                         And you may want to experiment with a tab control where you have a different tab panel for each general type of contact--that way you can modify the basic layout to better suit, say a "people" contact on on tab panel and a "company" contact on another.