Using Association Tables

QCubed also supports handling many-to-many relationships. Typically, many-to-many relationships are mapped in the database using an Association Table (sometimes also called a Mapping or a Join Table). It is basically a two-column table where both columns are foreign keys to two different tables.

QCubed allows you to define a set suffix for all Association Tables (the default is "_assn"). Whenever the code generator sees any table that ends in "_assn", it will mark it as a special table to be used/analyzed as an Association Table, associating two tables together in a many-to-many relationship.

With the Association Table in place, QCubed will generate five methods each for the two classes involved in this many-to-many relationship. In our example, we created a team_member_project_assn table to represent a many-to-many relationship between Person and Project.

QCubed will generate the following five methods in Person to deal with this many-to-many relationship:

• getProjectAsTeamMemberArray()
• countProjectsAsTeamMember()
• associateProjectAsTeamMember()
• unassociateProjectAsTeamMember()
• unassociateAllProjectsAsTeamMember()

QCubed will also generate the following five methods in Project to deal with this many-to-many relationship:

• getPersonAsTeamMemberArray()
• countPeopleAsTeamMember()
• associatePersonAsTeamMember()
• unassociatePersonAsTeamMember()
• unassociateAllPeopleAsTeamMember()

Note that the structure of these five methods are very similar for both objects (get, count, associate, unassociate, and unassociate all). In fact, you will also notice that this is the same structure as the reverse one-to-many relationship in our previous example. This especially makes sense considering that for all three examples, the object is dealing with the "-to-many" side of the relationship. Regardless if it is a one-"to-many" or a many-"to-many", the five methods dealing with "-to-many" is consistent.

Also, similar to our previous example, note that the "AsTeamMember" token in all these methods are there because we named the Association Table in the database team_member_project_assn. If we had use that actuale names of the two tables, as in person_project_assn, then the methods would be named without the "AsTeamMember" token (e.g. "getProjectArray", "associatePerson", etc.).

When associating two tables together that also use hyphens in their table names, the association table name can get confusing with all those hyphens. In this case, when naming the association table, remove the hyphens for the table names. For example, if you want to associate the table "project_location" with "customer_sites", you can name the association table "projectlocation_customersites_assn".

Person's Many-to-Many Relationship with Project (via team_member_project_assn)

• Person ID: 2
• First Name: Kendall
• Last Name: Public

Listing of the Project(s) that This Person is a Team Member of

• ACME Website Redesign
• State College HR Systzzzz
• ACME Payment System

There are 3 project(s) that this person is a team member of.

Project's Many-to-Many Relationship with Person (via team_member_project_assn)

• Project ID: 1
• Project Name: ACME Website Redesign

Listing of the Person(s) that This Project has as Team Members

• Kendall Public
• Alex Smith
• Wendy Smith
• Karen Wolfe
• Samantha Jones

There are 5 person(s) that this project has as team members.