Many to many relationships - Putting it together (part 3)

In my previous two articles, I talked you through some solutions for handling many-to-many relationships in LINQ-to-SQL. We examined solutions ranging from simple IEnumerable across the junction table, to a fully-fledged IList complete with Add/Insert and Remove/Delete support.

Of course, many-to-many relationships aren't just a problem for ORM. They're a diverse minefield of misunderstanding - and nobody is more at risk of sinking than your users. In this article, I examine some of the different ways that these complex relationships are presented to users.

The core elements of an interface representing a many-to-many relationship are the same (although methods of displaying them vary). The user normally requires (using Users/Groups as an example):

• A list of entities in the left-hand table (List of Groups)
• Add/Remove/Edit support for entities in the left-hand table (Add/Remove/Edit Group)
• A way to select a particular entity in the left-hand table in order to edit its relationships (Edit Group Members)
• A list of entities in the right-hand table that are participating in a relationship with this entity, and a way to remove them (View Users in Group / Remove Users from Group)
• A complete list of entities in the right-hand table (possibly only those entities which are not participating in a relationship with this entity) (Add Users to Group)
• Add/Remove/Edit support for entities in the right-hand table (Add/Remove/Edit User)

The user may also need to manage the relationship from the "other side" ("What groups is this user in?" as well as "What users are in this group?"). In addition - especially where permissions are concerned - we'll often have a chain of many-to-many relationships - e.g. Users belong to Groups, Groups have access to Categories, Categories contain Content. Anyone who's ever used a CMS or CRM system will have run into this problem. In this article I'm mainly interested in the relationship rather than adding entities to the participating tables.

Let's look at some of the ways these relationships are presented to the user:

The typical Winforms interface looks a bit like this:

The advantage of this interface is that it's easy to see at-a-glance what's going on. On the other hand, bulk add/remove can get clumsy - especially when there are many entities.

Surprisingly, I haven't seen this used very often in the browser - probably because of the post-back that would need to happen when you click "Add" or "Remove" - either that, or you would need a "Save Changes" button.

The typical web interface looks more like this:

This has the distinct advantage of being able to see participation of a single user at a glance. You can also bulk-edit participation of the selected entity easily.

The trouble with an interface like this is when your user needs to make a lot of changes all at once - or if they need to be able to control participation from both "sides" of the relationship.

I should take a quick aside to say that "Save Changes" is often not appropriate in administrative interfaces. You make your changes, you navigate away from the screen, you lose your changes. In a winforms application we can use "Apply, Cancel, OK" on a form to ensure that the user isn't guided away accidentally losing their changes (although I see this mistake often enough in winforms applications to offend me). On a website, however - with the site's navigation tools always on-screen - it's very easy for the user to click a checkbox then navigate to a new page, often losing their changes without warning.

As the data gets more complex, the interface can quickly get out of hand. A recent project with a client who was very concerned about the number of page loads & clicks ended up with a series of around twelve controls (e.g. content/category, category/user group access, category/admin, user group/user etc) looking a bit like:

While our client got what they wanted out of the interface (fewer page loads to bulk add/remove users), there are other problems - there's a lot of navigating around, so we needed to create several seperate "wizards" to create new entities and set their participation in the necessary relationships. That's a lot of interfaces to maintain.

One concept that we're quite familiar with from the desktop that hasn't yet caught on in the browser is drag & drop. Drag and drop is very intuitive because of the way it mirrors real life interaction - pick up the rubbish, put it in the bin; pick up the file you want to delete, put it in the "bin" - however there are a number of barriers to its use online.

The most important barrier is probably a technological one. While the desktop drag & drop paradigm is very mature now and a lot of developers are used to working with it, technology to replicate drag & drop in the browser feasibly (JQuery, Scriptaculous, ASP.NET Ajax) hasn't been around for very long at all. For now, browser drag & drop seems to be confined to layout customisation (iGoogle, the BBC, Facebook) rather than other functionality.

That's not to say that there aren't examples out there. Here's a pretty slick shopping cart (products/orders) using Scriptaculous, here's a neat student management tool (students/classes) with JQuery, and here's one I did with ASP.NET AJAX (users/user groups).

My impression is that the ASP.NET AJAX drag/drop framework isn't particularly well understood in the community yet - I certainly had some trouble finding help learning it - so I'll share the source code in my next article.

In the meantime, don't think you've solved the problem just because your ORM handles many-to-many. Spare a thought for your user!

< Previous Article: Many to many relationships in LINQ-to-SQL (part 2)
Next Article: Many to many relationships: Drag and Drop (part 4) >