GUI patterns to edit data with many-to-many relationship

Question

I often run into a situation where I need to come up with a GUI to edit data that has a n:m relationship. I'm looking for user friendly GUI ideas.

[table1]    |   /|\ [table2]   \|/    | [table3] 

Usually the GUI resembles something like this:

---

Grid that shows all items from table1

Add table3 item... _{(shows modal window with table3 items)}

---

Grid that shows all items from table3

---

After the user picked a table3 item, I add a new row to table2 and refresh the grids.

Disadvantages:

• You can only add table3 items to table1, and not the other way around;
• You can only browse table1 items and see related table3 items;
• I need to have one filtered grid of table3 items, and a similar one to pick new items;

My question:

Does anyone know a better way to visually browse and edit data that has a n:m relationship? Or any nice patterns that I could "steal" from existing software packages?

Answer 1

Solution 1

If the data sets are not too big, use a table and allow users to place checks in cells (table 1 is X axis and table3 is Y axis).

You can probably do this for larger table1/3 data sets as long as you allow users to filter or otherwise limit which values are displayed on x and y axis.

Solution 2

To quote from this page, "A many-to-many relationship is really two one-to-many relationships with a junction/link table".

As such, you can, as one solution, simply take your own solution and resolve your first 2 dis-advantages by having screens/dialogs to go table 1=>3 as well as 3=>1.

Not a perfect solution but at least provides all the needed functionality

Solution 3

Somewhat similar to your own solution:

1. Show a table based on table1, with:

   B. col1 containing table1 elements

   C. col2 containing a list of all elements from table3 already associated with this element from table1.

   The list can be either horizontal if there are usually few elements associated, or vertical (scrollable) if horizontal to too wide.

   The important part is that every displayed element from table3 has a "delete" icon (x) next to it to allow removal quickly.

2. Allow choosing which element from table1 you want to add mappings to.

   There are 2 ways of doing this - either add a checkbox to every row in your table, and have one button labeled "add relationships to selected rows" (wording needs improvement), or simply have a 3-rd column in the table, containing button/link for adding relationships to that individual row.

   The former is a good idea if the user is likely to often add exactly the same set of element from table3 to several rows from table1.

3. When "Add" button/link is clicked, you display a filterable multi-select list of elements from table3, with "add selected" button.

4. As in your solution (see my #2), this is a-symmetrical so you should implement a mirror UI for mapping from table3 to table1 if needed.