AnsweredAssumed Answered

Many-to-many relationships involving three entities

Question asked by MarkHarrison on Mar 10, 2009
Latest reply on Mar 11, 2009 by dinora

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. 

Outcomes