How to represent a bi-directional many-to-many relationship

I have students and parents apps which require a many-to-many relationship.  What is the current best-practice for representing this?

The first thing I tried is adding a third app: student_parent.  It works, but the workflow for managing it is awkward.

Next I’m going to try putting a table on each app referring to the other, i.e. Two 1:N relationships and synch them using the rest api on Submit.  This will be somewhat awkward to implement but potentially an easier workflow for the user.

Any other ideas?

Hello Jeff,

 

Regarding your question, to give you the best answer, could you be more specific about the relationship between these two apps?

(How will users use these apps, why do you want to connect these two apps, etc.)

 

Also, could you please explain how it’s awkward to manage the app when you try the first thing?

 

Thank you,

Chris

Thanks Chris,

I have a total of 12 Many-to-Many relationships in my solution.  For the sake of discussion lets consider the Student app and the Guardian app.  Students can have many guardians and Guardians can have many students.  Thus a many-to-many relationship. 

My first plan was to implement this using three apps:

  • Student:
    A related records control displays the read-only list of Guardians from the Student-Guardian app
    An [Add Guardian] action button launches the Student-Guardian app with the Student lookup field prepopulated. 
  • Guardian:
    A related records control displays the read-only list of Students from the Student-Guardian app.
    An [Add Student] action button launches the Student-Guardian app with the Guardian lookup field prepopulated. 
  • Student-Guardian:
    Has a Student lookup field and a Guardian lookup field.

All of this works as expected.

The awkwardness is in the number of clicks and unintuitive browser interactions required.  For example if you’re on the Student form and want to link to the Father

  1. Click [Add Guardian]  A new browser tab is created for the Student-Guardian form, and has the Student lookup already filled.
  2. Click the [Find Guardian] lookup and select the Guardian
  3. Click Save
  4. Close the Student-Guardian browser tab
  5. Select the Student browser tab.
  6. Reload the page to see the new values in the Student’s Guardian related records.

Then do all of that all over again for linking to the Mother.

It would be nice for the user if I could use a Table control on the Student app to allow adding a new row and populating it in place.  So to add a guardian:

  1. Click (+) on the table to create a new row
  2. Click the Guardian lookup in the new row to find the guardian
  3. Save

The workflow above does work and it is intuitive for the user, but only creates a one-to-many relationship.  i.e. the Student app knows who its Guardians are, but the Guardians don’t know who their students are.  I could also have a related records control on the Guardian app that points to the related students and require the user to update the Student-> guardian relationship as well as the Guardian->Student relationship, but that would be bad because there is nothing to ensure that the relationships are symmetrical and it would require even more unintuitive actions from the user. 

I tried writing js/rest to automatically synchronize the relationships.  I can make it work, but implementing synchronization for all CRUD actions was going to take a lot more code than I want to write and maintain.  Plus I want to use the same many-to-many strategy for the other 11 Many-to-Many relationships in my solution, so that strategy would be prohibitively complex and error prone.

Since many-to-many relationships are such a common entity relationship, I’m sure I’m not the first to try to model it in Kintone.  And I’d rather non re-invent a solution if its already been solved.

Thoughts?

Thanks,
Jeff

 

Hello Jeff,

Thanks for providing the detailed information.

In terms of ensuring synchronization, it’s better to use Related Record fields. But as you mentioned, there are many steps for users to take, so it’s not that efficient.

To avoid a one-to-many relationship using native features or related record fields, you can tweak the Fetch Criteria settings in the Related Record fields. Though there is a limit to what you can do with this, you can add other fields like “Classroom” to the setting to link the data.

If you want to use the table as you mentioned, a many-to-many relationship can be done by it, but it would be prone to error. So it would be better to use the native feature.

To make the system you already have any more efficient, the only thing I can think of is automating the lookup field. Upon entering a value in the Lookup field, you can use the GET method to determine whether the entered value exists in the other app. If the value exists, just let the field look it up normally; if not, use the POST method to add the value to the other app and then look it up.

I hope this helps.