1 2 Previous Next 15 Replies Latest reply on Dec 6, 2016 4:47 PM by Flyfisher2611

    many to many relationships?

    Flyfisher2611

      I seem to be struggling with the next stage of my solution!

       

      In am creating a database to help organise the plants I grow at our nursery. I have a table with a list of plants (also related to a different table) and I have a list of Label bins. (Each variety we grow has to have a variety label to put in the pot!) each label has an allocated bin no. A1, A2, B1, B2 etc.

       

      My problem!

       

      Each variety name may need more than one physical bin for example, each bin will only hold 1000 labels, so if I am growing 1500 plants I need another 'overflow' bin to hold the other 500 labels. In other words I need a portal in the Plant Detail table to allow me to allocate 2 bin locations to the variety record!

       

      However

       

      I also need to be able to allocate more than one plant variety per bin! For example I have two varieties of yellow double begonia - non stop yellow & go early yellow. I use just one generic 'yellow' label for both, i.e. Both varieties need to be allocated to the singe bin.

       

      My question is - is it possible?

       

      If so

       

      How can I do it?

       

      Thank you in anticipation of your replies

       

      Kind regards

        • 1. Re: many to many relationships?
          beverly

          this kind of Many-to-Many should then have a JOIN table with a way to assign as needed.

          beverly

          1 of 1 people found this helpful
          • 2. Re: many to many relationships?
            David Moyer

            Hi,

            you might look at this thread as an example of many-to-many usage.  This is a parallel to knowing what all is in any bin or which bin(s) contain any plant, or both.

            Make a list of related works

            1 of 1 people found this helpful
            • 3. Re: many to many relationships?
              coherentkris

              Maybe a little relational theory will help here.

              A many to many relationship is solved with an associative entity, commonly called a join table.

              Associative entity - Wikipedia

              Many-to-many (data model) - Wikipedia

              1 of 1 people found this helpful
              • 4. Re: many to many relationships?
                fmpdude

                Consider Teachers and students: a teacher can have many students and a student can have many teachers. So, this is Many to Many or M:M.

                 

                So, to handle this M:M you create a "join table" called (usually something like) STUDENT_TEACHER. This join table has primary keys from both the teacher and student. So, if teacher 1 had student ids 2, 3, and 4, your join table would look like this:

                 

                TEACHER          STUDENT

                1                              2

                1                              3

                1                              4

                 

                Or, another exmaple, STUDENT and COURSE (also M:M)

                 

                Below is a diagram using an "ERD" Tool which will actually GENERATE YOUR FILEMAKER Database if you want.

                 

                Then it's just a matter of writing the SQL or using FMP's built-in nice ways of handling these relationships.

                 

                ----

                 

                Try to diagram your relationships with tables. These visualization tools are very helpful when you're trying to understand and model data relationships (as well as communicate your design to others including stakeholders).

                 

                HOPE THIS HELPS.

                1 of 1 people found this helpful
                • 5. Re: many to many relationships?
                  keywords

                  In addition to the advice/comments already posted, it seems to me you have a bit more database organisation to do yet. You obviously need, and no doubt already have, a Plant Variety table to store fundamental details of each type of plant your nursery propagates. However, you also need a table to hold details of each specific Plant. Further, you need a table to hold details of your Bins. Connection between Bin and Variety is obviously a many to many, but the Plant table may well function to break this down to a pair of one to manys—a Variety may be in many Bins; a Bin may hold many Varieties; but a single plant can only be of one variety and in one place.

                  1 of 1 people found this helpful
                  • 6. Re: many to many relationships?
                    fmpdude

                    FlyFisher....don't forget to mark comments offered as "helpful" or "like" or even reply to them so future comments can help narrow in on your problem.

                     

                    None of us are paid for the time we spend helping folks like you. We do this free support since we like FMP and enjoy helping others.

                     

                    HOPE THIS HELPS.

                    1 of 1 people found this helpful
                    • 7. Re: many to many relationships?
                      erolst

                      fmpdude wrote:

                      [...] None of us are paid for the time we spend helping folks like you. [...]

                      You serious? So that's why my cheque hasn't arrived yet!

                      • 8. Re: many to many relationships?
                        Flyfisher2611

                        Hi

                         

                        Sorry if you think I have not been grateful for everybody's help, on the contrary, I am extremely grateful! This is the first time I have been back on to view your comments. We are very busy with Christmas trees and wreaths at the moment and in fact I had to visit Santa yesterday, so, as far as my little girl is concerned, everything else had to go on hold!

                        Please accept my apologies, I will be experimenting with everyone's suggestions this evening when we close.

                         

                        Kind Regards

                         

                        Alan

                        • 9. Re: many to many relationships?
                          Flyfisher2611

                          Just having a browse over lunch!

                          fmpdude, I had created a join table using

                           

                          Plant Variety ID from the Plant detail table

                          and

                          Bin ID from the Label Bin table.

                           

                          I tried to use this to try and create the portal but with no luck! I have not actually created a layout as such as i wanted the portal in the Plant detail layout!

                           

                          I will look again this evening, when I can spend some more time on it, but I suspect I need to do some more work on the join table!

                           

                          Kind regards

                           

                          Alan

                          • 10. Re: many to many relationships?
                            fmpdude

                            Sorry, wasn't implying you were not grateful or anything negative.

                             

                            Just suggesting that when you reply or mark things as helpful, you'll get better and better responses.

                             

                            HOPE THIS HELPS.

                            • 11. Re: many to many relationships?
                              fmpdude

                              komisch, dass Sie das sagen! 

                              1 of 1 people found this helpful
                              • 12. Re: many to many relationships?
                                Flyfisher2611

                                Ahrr! now I understand! Sorry probably due to my lack of experience with forums!

                                 

                                I hadn't even seen the 'Helpful' yes/no thing!

                                 

                                no wonder I'm struggling with Filemaker!

                                Thank you for pointing me in the right direction!

                                • 13. Re: many to many relationships?
                                  fmpdude

                                  Your post is important in that many folks struggle initially with M:M relationships initially. And, since M:M happens so often most folks here will jump on those posts to try to help.

                                   

                                  I use a tool called SQL Editor (SQLEditor for Mac OS X ) to create ERDs (DB diagrams). It's nice in that you can optionally create your FM database right from the diagram (or read an existing diagram into it).

                                   

                                  You really don't need a tool like that, but for larger projects (>10 tables) it becomes really more important. With 50 or more tables, forget a DB project without a standalone DB tool like that.

                                   

                                  I would make sure you can draw your tables on paper so they make sense and you have a clear path to the data you want. One mistake folks make is rushing to the computer too quickly without the solution understood up front.

                                   

                                  Speaking from experience (and the road of hard knocks), the more time I spend designing, the less time the implementation takes. The "coding" should be the easy part. (There's also lots of data to back that up in books like "Code Complete", MS Press).

                                   

                                  HOPE THIS HELPS.

                                  • 14. Re: many to many relationships?
                                    fmpdude

                                    You should post at least part of your Relationships diagram so others can jump in too. It's hard to picture what your overall schema looks like so far.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next