8 Replies Latest reply on Mar 12, 2009 10:12 AM by dinora

    Many-to-many relationships involving three entities

    MarkHarrison

      Title

      Many-to-many relationships involving three entities

      Post

      I am trying to create a database to track bibliographic references I have used in various papers for various classes, and that I might use in the future in various papers. I have created a table for sources (books, etc.), one for specific references (page number & text), and one to identify the papers in which a reference was cited. The references table and sources table have matching fields to allow all references taken from a particular source to be shown in a portal. That's easy. It is the relationship between the papers (projects) and the sources/references that is throwing me.

       

      Obviously, any reference can have only one source. However, any given reference can be used in many papers, and all papers will have many references, so there is a bidirectional many-to-many relationship.

       

      In the end, I'd like to have a layout that summarizes all papers, sources & references (sorted in that order), one that displays all references with their sources used in a given paper, and one that shows all papers in which a given reference with its source appears. I have created unique ID fields for each type of entity.

       

      I suspect that this is all MUCH easier than I am thinking it is. I am terrible about over-complicating the easiest things. I am also thinking that creating the layout is more of the problem than establishing the relationship between the tables, or at least just as much.

       

      Any thoughts will be appreciated. 

        • 1. Re: Many-to-many relationships involving three entities
          davidhead
            

          You have done a good so so far identifying and relating entities. Now you still have a meny to many relationship. This needs to be resolved down to two one-to-many relationships with a join table. Let's call that table UseReference for records that indicate the use of a reference. So you will have the following:

           

          Source ----< Reference ----< UseReference >---- Paper

           

          The USeReference table will have both an IDreference field and an IDpaper field to link it to the specific reference and paper records.

           

          Make sense? 

          • 2. Re: Many-to-many relationships involving three entities
            MarkHarrison
              

            Thanks - amazingly enough I pretty much worked this much out just after posting. Now, how can I (or can I) create a layout that summarizes everything, grouping the data according to project, then source, then references for one-stop referencing?

             

            Also, since I posted, I added to my "projects" (papers) layout a portal that will show references with their source ID and the text. This is great, but I also would like the title of the source to appear. To do that, would I need to link the title field as well?

             

            Finally, behind the last question there is my uncertainty about the proper use of multiple instances of the same table in the relationships graph. What exactly happens if you have two or more instances of one table? There is still only one table, right? Is a second instance of one table a way to handle a many-to-many relationship? 

            • 3. Re: Many-to-many relationships involving three entities
              davidhead
                

              The exact answers now depend on your approach to the relationship graph. Personally, I use the anchor-buoy method for most databases. So it may be useful to know what you have set up in your relationship graph so far.

               

              "...my uncertainty about the proper use of multiple instances of the same table in the relationships graph. What exactly happens if you have two or more instances of one table? There is still only one table, right? Is a second instance of one table a way to handle a many-to-many relationship?"

               

              The things in the relationship graph are call table occurrences or TOs for short. You can have as many TOs as you like for each table. Yes there is still only one table. 

               

              A second TO is not the way to handle a many-to-many relationship. That is handled with a new table like the UseReference table in the explanation above. So to be clear, when I talked about the UseReference table, I was talking about an actual table not a TO.

              • 4. Re: Many-to-many relationships involving three entities
                MarkHarrison
                  

                I knew you meant a separate "join table" in the example you gave, but I'm stil wondering what a second table occurrence would be used for.

                 

                Still, thank you, this really is helping a lot.

                 

                I tried to include a PNG  image of my relationships graph, but either the system is not Mac compatible or I'm doing something wrong. The insert/edit image feature just isn't letting me insert an image from my desktop.

                • 5. Re: Many-to-many relationships involving three entities
                  dinora
                    

                  Hi Mark

                   

                  I have the same situation as what you are describing

                   

                  Country----<Airport--------<Airport To City Table >----------City

                  Source ----< Reference ----< UseReference >---- Paper

                   

                  However when I try to do this the database wants to create a table occurrence for the aiport table.  So in effect I end up with Airport 1 and Airport 2 table

                   

                  Any ideas why??

                   

                   

                   

                  • 6. Re: Many-to-many relationships involving three entities
                    MarkHarrison
                      

                    Hi Dinora,

                     

                    I am still working on that. Of course, I was getting the same message. I am expecting a book to arrive this afternoon that may help me figure that out.

                     

                    Meanwhile, I created a solution in which my references table is connected to the projects table via the project ID, and to the sources table via the source ID. That simple. No duplicate tables.

                     

                    With this, in the projects layout, I have two portals stuck right next to each other. The first takes the source title from the sources table (which is only linked directly to the references table, mind you) and the other takes the page number and the text from the references table. The tables are placed together to look like one table showing source title, volume, issue, page, and text. So, for every project record I call up, I can see what I used in that project.

                     

                    In the references table, I have single fields to show the source from which the reference was taken (there is only one source per reference). I also have a portal that shows the date and name of each project in which that reference was used.

                     

                    In the sources table, I have two portals, one showing the projects in which that source was used, and one showing each reference taken from that source.

                     

                    I have not worked out how to have a single display summarize all of the information in one place, but as it is, my current arrangement would work just fine for me.

                     

                    Hope this helpsl 

                    • 7. Re: Many-to-many relationships involving three entities
                      davidhead
                        

                      Since we cannot post files here - grrr!! - if either of you wants to take this offline, email me - David at ulearnit dot com dot au - and I will have a look at your files and show you how to get to where you want.

                       

                      This forum is good to a point, then fails miserably. 

                      • 8. Re: Many-to-many relationships involving three entities
                        dinora
                          

                        I just emailed you.

                         

                        Thanks

                         

                        Dinora