SQLConfiguration SQLConfiguration

Database Relationships  «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).

Here are three tables to be related

After you click the ClientNo field in Clients, you drag the cursor over to the ClientNo field in Projects and let go of the mouse.

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. Then you click the Create button.

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

Now you repeat the same steps for the Projects and Consultants table. You would start by clicking the ConsultantID field Projects, the dragging the cursor over the ConsultantID field in Consultants and letting go of the mouse

The Edit Relationships dialog again appears.

Again, you check the Referential Integrity checkbox and then click the Create button

The diagram above shows the final relationship between tables