CCNC/CCNC Module 5/Designing and creating tables/Table Relationships/Many To Many
 |
Table Relationships |
Designing Relationships | Many-To-Many | Testing by Adding Data | Self Assessment | Summary & FAQs |
Contents
The many-to-many relationship
Upon completion of this tutorial the learner will be able to:
|
Designing the relationship
As was stated previously, the many-to-many relationship is implemented as two one-to-many relationships. The entity relationship diagram shows this very well. In the many-to-many relationship a cross reference (_xref_) table exists between the two tables with many records. For each Person joined to an Address, a record would exist in the _xref_ table with a reference to both the People and Addresses primary keys. The _xref_ table can also include a primary key for itself as shown here with the xref_id field. Keep in mind that the concatenation of the person_id and address_id would also be a candidate primary key. When designing the _xref_ table a brief relationship descriptor is useful. In this example there is an address_type.
Creating the relationships
To set the relationships between People and Addresses open the relationship designer and add the People (this table may already be referenced in the relationship designer), the Addresses and People_xref_Addresses tables. Drag and drop the person_id from the People_xref_Addresses onto the same field on the People table. This should create the relationship, confirm the creation by the new line drawn between the two tables. Drag and drop the address_id from the People_xref_Addresses onto the same field on the Addresses table. This should create the relationship, confirm the creation by the new line drawn between the two tables.
Right click on each of the new relationship lines joining the tables and set the relationship properties. Setting the update cascade is not as important as setting the delete cascade. Having the delete cascade set will ensure not data will be orphaned. Orphaning data happens when the 'parent' record is deleted leaving the 'child' record behind. In our example this would occur if you deleted an address record without deleting the corresponding _xref_ record(s).
Test your knowledge
|
|
