DBWebApplications 





Advanced Features  «Prev 

Creating many-to-many relationships

In database design, a many-to-many relationship is a type of cardinality that refers to the relationship between two entities A and B in which A may contain a parent record for which there are many children in B and vice versa. For instance, think of A as Authors, and B as Books.
An Author can write several Books, and a Book can be written by several Authors. Because most database management systems only support one-to-many relationships, it is necessary to implement such relationships physically via a third junction table (also called cross-reference table), say,
AB with two one-to-many relationships A -> AB and 
B -> AB. 
In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A and B).


These are the tables to be related
These are the tables to be related

The Edit Relationships dialog appears. Access sees the primary key in the Clients table, the foreign key in Projects, and knows this is a one-to-many relationship
The Edit Relationships dialog appears. Access sees the primary key in the Clients table, the foreign key in Projects, and knows this is a one-to-many relationship

Check the referential integrity checkbox so that one-to-many relationship will be reflected in the next screen.
Check the referential integrity checkbox so that one-to-many relationship will be reflected in the next screen


You can now see the first relationship established for the one-to-many relationship.
You can now see the first relationship established for the one-to-many relationship.

The Edit Relationships dialog again appears.
The Edit Relationships dialog again appears.

You check the Referential Integrity checkbox and then click the Create button
You check the Referential Integrity checkbox and then click the Create button

Final relationship between tables
Final relationship between tables