Creating Many to Many (N-to-N) Relationships
You are recommended to read "Understanding Relationships" before this section
if you haven't already.
Recommended video: Creating Relationships
How to Create Many to Many Relationship
- Click "Design" from main menu, select "Relationships".
- The relationship management window will open. Click "New Relationship" button.
- Select the first catalog you wish to create a new relationship for.
- Select the relationship type as "Many to Many Relationship".
- Select the second catalog.
Review the displayed information carefully to make sure that the suggested relationship properties reflect what you want to do.
Note that a Many to Many relationship is symmetric, that means each catalog is each other's "parent" as well as "child".
Interchanging the catalog names would produce exactly the same result.
The example given in the picture above shows a Many to Many relationship between Teacher and Student
catalogs. Each teacher has many related students. Each student also has many related teachers as they may have more than one teacher.
When you open a teacher record, you may display a list of all related students by clicking a menu button. The same is also true for student records.
A One to Many relationship alone would not serve this purpose.
Menu Name: The administrative name of the relational records. This name is not shown on record window but used
to easily identify the relationship whenever you modify or review the database design. You are recommended to keep the recommended
naming convention.
Display Name: The displayed title of the menu button which is shown on left side of the record window.
You may change both menu name and display name any time you want.
DESIGN TIPS
- You are recommended to avoid creating Many to Many type relationships whenever One to Many type serves the purpose.
More info about this
- Keep in mind that every relationship you create adds to the overall complexity of your database which in turn makes it more difficult to
both manage and improve the design of your database.
- You may need to create and experiment with a number of relationships before you achieve the desired functionality.
Delete any unused/experimental relationship as soon as possible.
- The best way to work on a relationship model is to create a separate database dedicated just for experimental purposes. To do this, export
the application template of your production database, create a new database, import that application template and work on that
test database until you get the desired result. After that, you may re-connect to your production database to apply the necessary design changes.
- Creating multiple copies of the same relationship usually indicates a poor design approach. If you are tempted to create many copies
of the same relationship, review your design again or consult a professional.
Displaying Relational Data on Record Windows
When you create a Many to Many relationship, a menu button is automatically added to record
windows of both of the catalogs. Clicking those buttons will display the related records from the parent catalog.
You will also be able to either create new or add an existing record to the related record list.
It is possible to create dedicated views for related record lists.
See Menu Designer for more information.
Modifying a Relationship
Once created, changing the relationship type (1-to-N or N-to-N) or changing any of the catalogs is not allowed.
If you need to change these, you should consider deleting the existing relationship first which will also
remove all the related data information from records.
You may at any time rename menu button titles.
Deleting Relationships
Deleting a relationship removes both of the relationship menu buttons shown in the record windows of each catalog.
- No actual records are deleted from your database, however, the "related data" information for all records in both catalogs will be lost.
- You will no longer be able to view related (a.k.a. child) records from a record in either catalog.
- If you have created computed fields which were configured to display or process data from related record(s) in either catalog, they will
stop functioning and become obsolote fields. You should remove those computed fields.