5 Replies Latest reply on Aug 19, 2009 9:46 AM by comment_1

    Many-to-Many relationships nightmare

    erenerm

      Title

      Many-to-Many relationships nightmare

      Post

      Here is the information I have: 

       

      Many records of different art works. Artwork 1 needs bibliography that includes several different books BookA BookB BookC and page numbers for each of those references. The problem is that Book A also connects with Artwork 2 and Artwork 3 while Book C has its own set of unique artworks that it references etc etc.

       

       

       

      I am in the middlle of creating my first database as part of a project. I know that this is a many to many relationship, but I do not know how to program it into my database. Help?

        • 1. Re: Many-to-Many relationships nightmare
          comment_1
            

          You should have (at least*) three tables: Artworks, Books and References. The References table is a join table between Artworks and Books:

           

          Artworks  -< References >- Books

           

          See a basic demo of a join table here.

           

           

           

          Note: 

          (*) You should probably also have a fourth table of Pages - a child of References.

          • 2. Re: Many-to-Many relationships nightmare
            erenerm
              

            I should've explained the situation in more detail.

             

            Right now I have four tables for this:

             

            Art Objects

            Bibliography

            Art Objects and Bibliography Join table 

            Page Numbers. 

             

             

            Page  Numbers is connected to bibliography (many to one, respectively)

            Bibliography, Art Objects are linked in the Join table by the foreign IDS in this table. 

             

             

            Essentially what I have is a portal on the Bibliography layout and a portal on the Art objects layout. The portal on the art objects layout is supposed to show the bibliography it is linked to along with a page number I can simply type in. The portal on the bibliography shows the art objects that it is linked to, along with the page number I typed in. 

             

            The problem lies in the fact that I cannot link several art objects to the same book without it creating a new record in bibliography for the book. So I have several of the same record with only one art object in it so that I cannot view it on one portal. Then when i edit the relatinoship so that it does not allow entry for any book, it will not allow me to link it in any way. 

             

            What I would ideally like to do is have a window pop up with all the different values for the bibliography in the form of a check boxes and check as many books as I would ike. When returning to the art object layout, I would like to see all these books listed in the portal and when going to each of these records on the bibliography, I would liek to see the art objects listed in the portal on that layout. Is there any way I can program this?

             

            I apologize btw for using such layman's terms for all this, I really dont create these for a living, im just creating it for an exhibition. 

            • 3. Re: Many-to-Many relationships nightmare
              comment_1
                

              erenerm wrote:
              What I would ideally like to do is have a window pop up with all the different values for the bibliography in the form of a check boxes and check as many books as I would ike.

              So it sounds like you already have the correct structure in place*, and all you need is a more convenient user interface.

               

              One way to do this could be to define another relationship between ArtObjects and a new ocurrence of Bibliography, using the x relational operator. Place a portal based on this relationship on a layout of ArtObjects, and put a button in it that creates a new join record joining the current art object and the clicked book.

               

               

              Here's a further extension of the same idea.

              • 4. Re: Many-to-Many relationships nightmare
                erenerm
                  

                comment wrote:

                put a button in it that creates a new join record joining the current art object and the clicked book.



                 

                 

                You've been a great help. I did all that and am now having trouble scripting that button to perform the same script as you  have in the portals to portals copy. I'm thinking of opening a new layout that just lists the bibliography records so that I can click all of them and have those records show up in my portal on the ArtObjects layout. How do I do this?

                • 5. Re: Many-to-Many relationships nightmare
                  comment_1
                    

                  erenerm wrote:
                  I did all that and am now having trouble scripting that button to perform the same script as you  have

                  Perhaps you should provide a more detailed description.