1 2 Previous Next 24 Replies Latest reply on Mar 7, 2010 5:03 PM by Dasa

    many to many relationship

    Dasa

      Title

      many to many relationship

      Post

      Hi, I am an amateur using FM 10 on Mac 10.6

      I have a database for art objects with these tables:

      art object (has one contact/location at a time) WORKING

      contacts (can have many art objects at a time) WORKING

      exhibitions (can have many objects and an art object can have many exhibitions) NOT WORKING

      How do I make a functional many to many relationship between art objects and exhibitions?

      Each table has one ID field.

      Thank you!

        • 1. Re: many to many relationship
          Jade

          One approach is to add a join table ("Exhibition_Object") that contains one object_id and one exhibition_id on each record.  There may be other fields on this table that are uniquely associated with each exhibition - object combination.

          This way, one object can be assigned to multiple exhibitions (obviously not at the same time) and one exhibition can have many objects.

           

          The  relationships  between the Table Occurrences should look like this:

           

          Exhibitions  -< Exhibition_Object >- Objects >- Contacts

           

          ( -< denotes a one-to-many relationship )

           

          The portal(s) should be based on Exhibition_Object so that the ID's can be posted in the join table.  

          • 2. Re: many to many relationship
            Dasa

            Actually one object can have multiple exhibitions at the same time, I want to track each object's exhibition history.

            Does that make a difference?

            Thank you!

            • 3. Re: many to many relationship
              Jade

              I think the structure still works.  It may be simpler since you no longer have to verify that the exhibit-object combination is unique by date/time and location.

               

              Normally, you would want to delete the related records in the join table when either of the parent records (Exhibit or Object) is deleted.  Instead, you can add date/time/location and some lookup fields for the Exhibit and Object descriptions on the join table to create your history.  You would then require another means of managing the history records (display, report, archive, delete)

              • 4. Re: many to many relationship
                Dasa

                Sorry, I've been monkeying around with it and it's not working.

                What am I doing wrong?

                I created a table EXH_OBJ with Exh # and Obj # and title, medium, dimensions and retail fields.

                I joined EXH_OBJ to EXHIBITIONS on Exh #

                Objects to Contacts are already joined on Contact # (this is working)

                I am trying to get a portal (based on EXH_OBJ) to work on the EXHIBITIONS screen and it's unresponsive

                Wish I could include a screen shot.

                Thank you!

                • 5. Re: many to many relationship
                  Dasa

                  oh, and EXHIBITIONS are linked to OBJECTS on object #

                  • 6. Re: many to many relationship
                    Jade

                    EXHIBITIONS should only be linked to EXH_OBJ by Exh #  (not linked directly to OBJECTS)

                     

                    OBJECTS should also be linked to EXH_OBJ by Obj #

                     

                     

                    To post a screen shot, you must first upload the screen image file to a web service that can provide a URL (http:…) address of the image (e.g. photobucket.com).  Then use the "insert/edit image" button on this forum when you post.

                    • 7. Re: many to many relationship
                      Dasa

                      Is this right?

                      • 8. Re: many to many relationship
                        Jade

                        I am sorry, Dasa.  I can not see an image; only a "?".  

                         

                        Make sure your URL address does not have [img] at the start and [/img] at the end. 

                         

                        Is it something like this?

                         

                        • 10. Re: many to many relationship
                          Jade

                          I still cannot see your image.  Take a look at the example that I posted previously. Maybe it will help.

                          • 11. Re: many to many relationship
                            Dasa

                            http://www.flickr.com/photos/dasa_bausova/4395942634/sizes/o/

                             

                            Okay I am trying a link to the image...

                            Yes, it looks like I got it except my join from contacts to objects (ART) is more complex?

                            • 12. Re: many to many relationship
                              Jade

                              The links to the join table (Exh_Obj) look right.

                               

                              I do not know your requirements but would question the following:

                              • complex link between contacts and art?  Just a relationship between person # should work

                              • object # field on contacts?  Probably not required.

                              • Exh_Obj # on EXH_OBJ.  Since you may be using this table as a history, it would be advisable to add a unique serial number.

                              • 13. Re: many to many relationship
                                Dasa

                                I have a portal for tracking which objects people own on the contacts screen, and I show where each object is located at the moment on the art object screen, that's why I thought i needed the object join, maybe that's not right?!?!?

                                Your last point I don't understand. On what should I have a unique serial # in EXH_OBJ? Should I have a new field?

                                Thank you!

                                • 14. Re: many to many relationship
                                  Jade

                                  It should be sufficient to just put the person # on the Art Object record to create the relationship to the contact.  (I assume person # is a unique auto-enter serial number.)  The location should be on the Art Object. (I assume an object cannot be in two places at once.)

                                   

                                  Each table should have its own unique ID (generally an auto-enter serial number) so add a field to the EXH_OBJ table for this.

                                  1 2 Previous Next