8 Replies Latest reply on Jul 23, 2016 11:02 AM by clayhendrix

    Help creating joint table

    jefflambert

      Hi, total newbie here so bare with me please:-)

       

      I finished watching the essential training on filemaker pro 15 on lynda.com but I still have some question which weren't answered so I figured I should join this community and see if I could get some help.

       

      My boss has done a database for our company like 15 years ago. We updated the database made of multiple files to FM 14, but it's a real mess and he asked me if I could fix it. I'm a professionnal retoucher, not a programmer, I have no other knowledge of databases either. So my question might seem really simple to you guys. In the cours on lynda, it was really emphasized that you should NOT have many to many relationship between your tables so I'm trying to figure out how we should make the joint table but since our need exceeds what was shown in the class, I don't know how to proceed.

       

      Here's what we would like. Sorry if it's in french, it's color coded though;-)

      whatweneed.png

       

      Here are my two attempts at creating my joint tables

       

      solutionA.png

      solutionB.png

       

      The other question I have is concerning the primary key vs the foreign key. Can the foreign key be in multiple tables? For exemple, I have the ClientIDfk in both Dossiers and ClientsEmployé, is this OK, or should I create another foreign key just for those relationship? My solution A would be simpler but can I "group" my need for joint table like that or I need to split them like Solution B? If I have to go with Solution B, would I have to also create an other relationship between Clients Employé and Dossiers Employés? Can I join two joint table like this? Would it be necessary for what we need?

       

      Thanks for any advice!

      Jeff

        • 1. Re: Help creating joint table
          coherentkris

          primary key is on the one side of an entity relationship. fk is on the many. It does not matter what, or how many, entities the fk is in as long as their is a way of relating the data elements and that the arrangement makes logical sense.

          I would suggest that you forget about building tables for a few minutes and think about how the entities present relate to each other. You only need a associative entity (join table) when the relationship is many to many.

          • 2. Re: Help creating joint table
            clayhendrix

            Besides the fact that I do not understand French, the first question is the same question I would have if the images had been in English and are similar to coherentkris questions. However, to help my brain, please let me know if the following translations are correct.

             

            Fournisseur = Provider

            Clients = Customers (a.k.a. Clients in English)

            Dossier = Folder

            Bon de commandes = Purchase Orders

            Employees = Employees

             

            1.) Are the above translations correct?

            2.) To whom does the Dossier belong?

            3.) Are Employees, employees of the company for which you work?

            4.) Who creates purchase orders, clients, providers, employees?

            5.) Do Clients have multiple Dossiers?

            6.) Do Dossiers have multiple Clients?

             

            I hope that isn't overwhelming and can help you get the information you need. I'll be here for awhile.

            • 3. Re: Help creating joint table
              clayhendrix

              To specifically answer your question about primary and foreign keys.

               

              Every table should have one primary key. A table should not have zero primary keys and should not have more than one primary key. Sometimes there are exceptions, but usually at your level of comprehension, that is a good rule to follow. That is not an insult. I am just trying to make it easier for you until you become a more advanced user. Please understand that.

               

              Tables may have multiple foreign keys.

               

              You do not create foreign keys. A foreign key is not created, it is simply how a record in one table is linked to a record in another table. A record will have a primary key in the table in which it was created. The primary key for that record (if it has one) is simply how that record is related to another table. For example, a Provider will have a primary key (such as an ID number). The Providers foreign key does not actually belong to the provider, it is the primary key of the Client. Another way to explain that is that a Provider does not get a new Identification Number for each new Client the Provider has. The Clients that are associated with that Provider will have the Provider's primary key entered as their foreign key.

               

              Customer Jacque (ID 45) is associated with Provider Angela (ID 3124)

              Customer Claude (ID 39) is associated with Provider Angela (ID 3124)

              Customer Angelique (ID 42) is associated with Provider Angela (ID 3124)

              Customer Jacque (ID 45) is associated with Provider Vladamir (ID 3315)

               

              Providers have multiple Customers and Customers have multiple providers. Primary keys are assigned to Customers so that each Customer has his/her own ID. Primary keys are assigned to Providers so that each Provider has his/her own ID.

               

              Screen Shot 2016-06-20 at 5.36.09 PM.png

               

              You can see in the attached what I mean and how a join table is needed. I'm sure you've seen an image like that many times. You really have to build your own a few times before you understand. I hope that _pk and _fk do not confuse you. There are many ways to name fields. I use _pk at the end of a field name to indicated that it is a primary field. I use _fk at the end of a field name to indicate that it is a foreign key. I use a lot of other additions to field names to help me, but I left them off of this example because they are a lot to comprehend all at one time.

               

              I hope this helps you. If not, just reply with questions.

              • 4. Re: Help creating joint table
                jefflambert

                Hi Clay thanks for your help!

                 

                Your translations are correct.

                 

                I'll try to describe the "What we need" graphic with words.

                 

                Clients have many Folders (Dossiers) but each Folder belongs to only ONE client

                Folders may have many Purchase Order, but they can't belong to more than one Folder

                Each Folder has more than one Employee working on them (these are OUR employees)

                Employees can work on many Folders and for many Client

                 

                On the other side of things, we have providers

                Providers can do things for many Folders and many Clients

                Purchase order can belong to only on Providers

                 

                In the What we need graphic, I've illustrated the many side with a double dash. Since I have a lot of many to many relationship, that's why I know I need the joint tables.

                 

                I thought I understood the Primary and foreign keys but your statement that you do NOT create the foreign key thru me completely off. I though I had to create the PK, then create a FK in my other table. For example, each client has a Client ID, this would be my primary key for the clients. Now if I go to my folder table, I will create a field which I will name Client ID fk, and will tell FM to get the data from the field Client ID in the table Client. So, in my mind, I still need to create the field, just not the data that goes in it. Is that what you mean by you do NOT create the fk?

                 

                So with your answers and the one from coherentkris, I can have build my joint table and put multiple fk in it so that I can make a report of say what Provider 123 has done for Client 456 and sort it by Folders for example. I could also see what provider does the most job for us and other question in those lines.

                 

                Are my assumptions correct?

                Thanks!

                • 5. Re: Help creating joint table
                  clayhendrix

                  I'm glad my reply was helpful.

                   

                  You do create the field for the foreign key. You enter data into the foreign key that matches the data in the primary key in the other table to relate records, I believe you understand that based on your reply.

                   

                  When you create the join tables that are needed for the many-to-many relationships, several circular relationships occur and FMP makes multiple occurrences of the Folders table on the relationship graph, as shown in this image. Screen Shot 2016-06-22 at 6.29.13 PM.png

                  I hid all of the fields so you could see the similarity to the image you created above.

                   

                  It has prompted more questions that might help to simply the design. Please answer these questions:

                  1.) What is the purpose of folders? Are folders projects?

                  2.) Are folders a way to segment or categorize work?

                  3.) Do you ever need to "see" what is "in" a folder?

                  4.) I keep thinking of a physical folder that organizes documents or files, are the folders in your database items that hold documents/files/things together?

                   

                  If you are concerned about giving away trade secrets or confidential information, use another type of industry to help me understand.

                   

                  For example, I can see that if your company did maintenance to busses, that your Employees are associated with your Clients. Your employees hire Providers to perform the maintenance using Purchase Orders. Or perhaps your Employees do the maintenance for your clients, but use Purchase Orders to order parts/supplies from Providers. In that example, the folders might be busses or the folders might be busses located in a certain region.

                   

                  Help me understand what purpose Folders serve, if you still need help with the database design. With that many occurrences of a table on the relationship graph, there are some things that will be harder when you start to build layouts and reports to get the information you mentioned above. Click here to be linked to a good discussion about Table Occurrences versus Tables in FMP. If it's too confusing, just focus on helping me understand the answers to the questions above.

                  I'm super curious and like to work on issues like this. I am far from the most capable, but someone will help if we both get stuck.

                  • 6. Re: Help creating joint table
                    jefflambert

                    thanks Clay,

                     

                    To answer your question

                    1) Dossiers (or what I had referred in english as Folders) are projects we do for clients. For example, an Annual Report is a Dossiers, a web site is another, a flyer another and so on. These Dossiers are identified by a an abbreviation on the client's name and a number. So for exemple "SI-235 flyer 2015" would an example of a full name for a Dossier.

                     

                    2-3-4) the purpose of the Dossier for what FM is concerned is just to keep track of what work we do for each client, and what the purchase order we have on each of those Dossier. Accounting is done in another software, as is time spent on each Dossier. Those Dossier are created on our server, and files to create the artwork are stored there, but this has nothing to do with FM.

                     

                    I have to put this project on the back burner  a bit, vacation time. But I'll check your answers and references as soon as I can.

                    Thanks for taking the time Clay.

                    • 7. Re: Help creating joint table
                      jefflambert

                      I've talked to my boss and he thinks we can simplify the whole thing a bit.

                       

                      We basically have the same needs, but we can deal with less tables by grouping the table from Providers, Clients and our company. Doing the same thing with Contacts which would include our contacts from company, providers and employees. So it would look something like this:new_diagram_FM.png

                       

                      Now the thing I don't know is, for each Purchase orders, I will have both the clients name and the clients company, but also the providers contact name and company AND, the employee from our company who has made the purchase order. So that's three contacts and two company essentially for each Purchase order. So in that case, do I need another table, or link between Purchase Orders and contacts and Companies?

                       

                      I also added the items' table for easier processing. These will not be like products and they are not something that would keep coming back either. For exemple, it could be like 50 color prints, 300 indigo pamphlet of 28 pages. The chances that we reuse an item of 300 indo pamphlet of 28 pages is pretty slim, so it's a one shot deal but my boss thinks it's a better practice to have it stored in a separate table.

                       

                      So what do you guys think, am I on the right track?

                       

                      TIA

                      • 8. Re: Help creating joint table
                        clayhendrix

                        I think you are on the right track.

                        1. The relationship between Compagnies and Projects should be a many-to-many with a join table, if multiple Compagnies work on one Project AND a Compagnie will work on multiple projects.
                        2. Add a table for Lines of the Purchase Orders and related Lines and Purchase Orders.
                        3. Add a table for Items and relate it to the Lines table.
                        4. Are the contacts you need on a Purchase Order already associated with the project? That will determine if you need additional tables or table occurrences to get the correct info on the Purchase Order.