5 Replies Latest reply on Mar 6, 2009 2:05 PM by deltatango

    New user table design and relationship question



      New user table design and relationship question


      I would like to make a database with three tables Companies, People, and Products.

      The Companies table would contain Company name, location, People (multiple), and products (multiple).

      The people table would contain People's Names, Affiliations (multiple), info, etc.

      The Products table would contain Product Name, Cost, technology, etc.

      When I add a record to the company table I would like to see the products that I added show up in the products table where I could then add the details for the products.  The same goes for people.  I would like people from the company show up in the people table.  

      Is my table design reasonable?   If this is not obvious, I don't know much about databases.  Can you recommend a different design for this data?  What type of connections should I use?  How should I deal with multiple products, multiple people and multiple affiliations?




        • 1. Re: New user table design and relationship question



          Thank you for your post.


          What you describe is possible.  However, since a person can have multiple affiliations, you will probably need an affiliations table, too.


          Assuming your tables are all set up correctly and linked, to one another through some kind of "key" field, you can definitely add information to a Company Table, and add information to the Products table that is automatically linked to that specific Company.  Setting all of this up will take some time.


          I would recommend creating your tables, and then study how to set up relationships.  This will require the most amount of time and understanding (and frustration for many first time users).  This will get you familiar with the concepts and know how to proceed at that point.


          If you have any questions or need clarification, let us know.



          FileMaker, Inc.

          • 2. Re: New user table design and relationship question

            Thank you for the quick reply!  Could you elaborate on the term "key"?  I made the tables and experienced my first dose of frustration before going to the message board. I tried linking the Companies table to the Products table through the product name, but I have been unable to add new values to the Products table this way.  I am willing to spend time searching for solutions, but I that I am not getting much traction because I am short on terminology for searching Filemaker help or the web.  I feel like this should be a common database setup.  Is there an example somewhere that will get me moving?


            thank you,




            • 3. Re: New user table design and relationship question

              You might find it useful to look at the White Paper for FMP Novices

              on this site



              I know I did. 

              • 4. Re: New user table design and relationship question



                A "key" field is something that will allow you to connect two tables together.  It sounds like you did that with the Product Name.


                Return to "Manage -> Database..." from the File menu and click on the Relationships tab.  You will see a graphical representation of your tables, and there will be a line connecting the Product Name from the Companies table to the Product Name in the Products table.  Halfway on the connecting line is an icon.  Double-click on that icon and an "Edit Relationship" dialog box appears.  One side has the Products table and the other side has the Companies table.  At the bottom of the Products table, check the option "Allow creation of records in this table via this relationship".  This will then allow you to add new values to the Products table.  This should help your understanding with linking two tables together.


                I would also highly recommend the white paper that "davidanders" pointed out (Thank you!).  It is very informative; especially the section on "key" fields.



                FileMaker, Inc. 



                • 5. Re: New user table design and relationship question
                     Besides that, it is hilarious! I read it even had a good laugh, because I thought "How many times have I done that!!!"