4 Replies Latest reply on Mar 12, 2013 8:39 PM by luisrosario

    Table relationship

    luisrosario

      I represent a company for which I sell, or rent machines and also give technical assistance in these machines.

      I'm trying to create an application that will help me in my daily control.

      I'm having trouble making the relationship between tables.

      I would appreciate if someone can help me.
      Starting as a principle that for every client I have the following situations:
      Each customer can have one or many machines.
      Each time I visit a customer, i have to do a visit report where I write down the problems or

      malfunctions of the machines, this report must include the date of the visit.
      I need to chart dates for every 60 days to make a visit to each customer.
      Every 15 days I have to bill for the company I represent, the various visits I made to my clients, where I include the travel expenses and the agreed value for each assistance.

       

      I think I need 5 main tables, Customers, Machines, Visits/Dates, Invoicing and Visit Reports.

       

      Where should I place the primary key and foreign key, and how to relate them?

       

      FileMaker Pro 11 Adv.

       

      Thanks

        • 1. Re: Table relationship
          keywords

          Where should I place the primary key and foreign key

           

          You will end up with more than one of each. Each table should have a primary key which uniquely indentifies each record in that table. Any table that has a relationship to another table based on the primary key field of the other table will have a foreign key to match to the primary key in the related table. that much is a start at least.

          • 2. Re: Table relationship
            luisrosario

            thanks. Yes... i´ve done that, but i think i must have at least one more table for the many-to-many cases. and that is my bigger problem, where should i create and who many tables more hsould i create?

             

            Thanks for your help.

            • 3. Re: Table relationship
              timwhisenant

              Hi,

               

              Resolving many-to-many relationships is best done with a join table. The join table is a little different in that it exists not for the recording of data, but to manage the relationships between data from other tables. An example might be Users & groups, 1 user could be in many groups and many users could be in a group or stated in other words many users can be in many groups. The join table GroupUsers would only have foreign keys one to Groups and one to Users, but no primary key because each record exists only to connect the two. So to add / remove a user from a group boils down to adding or deleting a record ( userid & groupid combination ) from the join table.

               

               

               

              Hope this helps,

               

              Tim

               

              Message was edited by: timwhisenant

              • 4. Re: Table relationship
                luisrosario

                Thanks, i think i understand