Create a Many-to-Many Relationship

A relationship between tables is usually one-to-many affair. In other words, one record in the master table relates to many records in the details table. For example, one class has many students assigned to it. But what if you need to assign MANY students to MANY classes?

To accomplish this, you can add a third table that acts as a go-between and handles all the possible combinations involved.

Tip: If you're worried about how this may impact your application, test it out on a copy first. To do so, make a copy of your application with data, then follow the instructions below to see if this solution is the one you're looking for. If you like the way it works in the copy, make the same changes to your live application.

ATTENTION PROJECT MANAGERS! ----> Stop and think. Lots of project managers ask us how to create a many-to-many relationship between resources and tasks. Before you set up your project management application to assign multiple users to a task, think hard about what a task is and who is actually in charge of completing it. Can the job be broken down into individual tasks that each person completes? What about accountability? Who's really responsible if the task falls through? In fact, which of your assignees are responsible for actually marking this task completed? Sometimes, a many-to-many relationship creates more problems than it solves. For example, this kind of setup makes it difficult to send email notifications to assignees when a task record changes. So, whenever possible, work hard to retain a traditional one-person-to-many-tasks setup. An alternative is to have multiple user fields on a single task.

To create a many-to-many relationship:
  1. Create a new table to serve as the intermediate table.

    Add a new table to your application and give it an appropriate name. For example, you might call a table connecting Students and Classes the Assignments table.

  2. Create a relationship between the new table and the old tables.

    Create two relationships. The new, intermediate table should be the details table to each previously existing table. In other words, the new table is the MANY in the one-to-many relationship with both original tables. (What's a details table? Read about relationships.) When you create each relationship, make sure to let Quick Base add all the fields that go with it. The program does this automatically, unless you interfere and turn off options to that add summary and lookup fields.

  3. Add lookup fields to the relationship.

    If you want to draw additional information from either existing table into the new table, add lookup fields to the relationship. For example, you may want the Assignments table to display the class description and instructor name from the Classes table. The more information you draw into your new table, the more useful it is—not just for creating assignments, but also for creating reports and charts. In fact, all your reporting will be based on the intermediate table from this point forward. So, don't be stingy about adding lookup fields.

  4. Delete unnecessary fields from master tables.

    If there are fields in the original tables that track information that's now in the new intermediate table, you'll want to delete those fields (for example, a Class field in the Students table that you'll no longer use) and let the new table handle all the work.

  5. Create automated email notifications for the new table.

    If you have email notifications, subscriptions or reminders set up, you may need to replicate these on the new table. For example, if your Students table notified a person when you assign them to a class, you need to create that notification for your intermediate table (Assignments) because that's where you now make class assignments. (Read more about automated emails.)

How it works

Now, whenever you want to add information that links your two original tables, you do so by adding a record to the new table. For example, if you want to sign a student up for a course, you'd add a new record to the Assignments table. Within the new record, select the class and select the student. To assign multiple people to one class, create multiple records in the assignments table—one for each combination of person and class. Quick Base makes this easier by adding an Add Assignments button to each master table. The result? From within a Class record, you can click the Add Assignments button to assign a person to that class. You can do the same from within a Student's record. The button adds a new record to the Assignment table that joins a Student and a Class.

Reporting - Create reports for the intermediate table

You should do most of your reporting from within the new table. For instance, say you want to see a list of everyone in a particular class so you can send them an email. Create this report within the Assignments table. To do so, it's imperative that you create the necessary lookup fields in Step 3 above so you can set matching criteria on Class Name or Instructor. If not, add the lookups now.

Setting access permissions

After you create a new table, you may need to edit access permissions to its contents. Read about permissions. If you change the structure of your application, you may need to change your whole approach to permission settings. For example, say you want students to see only their own class assignments. What you need to do is limit access based on a user's relationship to each record. Read how to implement this kind of access rule.

Note: Do you need to hide information from your users or are you just trying to focus their attention on the information that interests them? If it's a question of security, set access permissions. If you just want to make your application easier to use, don't set permissions, create custom reports instead. Not sure what to do? Read more.

Related Topics:


Go back      |       |   

© 1999-2018  QuickBase, Inc.  All rights reserved.  Legal Notices.