Forum OpenACS Q&A: What is the best way to implement many-to-many relationships between ACS Objects?

I am working on the Bug Tracker and we need to implement a
many-to-many relationship between bugs and patches, both of which are
ACS Objects. What is the best way to do this? Do we simply use a
mapping table, like in the good (or bad?) old days, or is there a more
generic approach that we should use? Any examples of how this is done
would be much appreciated!



You simply use a mapping table.  Period.  You can find examples in
any book on relational databases.
Although its worth pointing out that acs-rels is essentially only a mapping table (of general nature)...

I've never been entirely convinced about using acs-rels as the 'catch all' mapping mechanism.... largely cos I'm suspicious about how well this would scale and so forth.....

And (i think) its true to say that the only reason to use acs rels is when you want other acs'ities (such as permissions) to work??

In most cases we implement a separate mapping table. Also, thats not a (good or bad) old thing... thats a pretty standard DB thing to want to do.


Excellent point.

I found this quote from the design docs

"The advantage of registering this table as a relation type is that in principle the OpenACS 4 object system could use the meta data in the types table to do useful things in a generic way on all relation types. But this mechanism doesn't really exist yet."

So is there anything a centralized table and relation_types can do that a mapping table for each relationship type cannot do?


The part from the doc. that you quoted is the key.  More precisely the
"in principle" part.

If you employ the relation types mechanism you are making your code more
complex, less readable, harder to maintain now for an unknown potential benefit
in the future.

Another option is to go for a more straightforward solution now that is
sufficient for your current requirements.  This might mean that at some point
in future you might have to rewrite some of your code to switch to the relation
types solution in order to gain whatever benefits it might bear.

Kent Beck in his book on XP
reminds that you should also take into account the interest on the investment
you are making today, which will pay off in more or less distant future.  Of
course, if it turns out that the benefits in the future never pay off that
investment is completely wasted.

I can tell you a true story about using generic relations in (Open)ACS from a
team working on a very high-visibility project for a Global-50 company:

As you probably know, generic relations are used for representing n-to-n
relationship in the permissions system.  After more than a year of working with
OpenACS permission system, and after the system has been successfully put into
production, the team got a change request to implement a functionality to merge
two users.  As merging users touches in so many tables in the system, including
the permissioning tables, the team decided that it was about time to simplify
the permission system and get rid of the relation types.  As a part of the same
effort they also removed the parties table and put the email field back into
the users table.

Improved performance and code maintainability have proven that that was the
right decision.

This example shows that, in addition to wasting the investment, and the
interest, you might end up spending even more money on revamping the system
just to avoid wasting more resources as you go forward.

I have mixed feelings about the general relationships facility.  I don't see the justification for making every row in acs_rels be an object.  On the other hand a general mapping facility is useful - why reinvent the wheel on a per-package basis when you're mapping object relationships?

This is one area I've been thinking we should poke into for 4.7.

As far as parties, general relationships and permissions, there's a lot of room for improvement in performance here.  I don't think that parties need to be scrapped to achieve that.  Scrapping parties would require the writing of the mother of all upgrade scripts ...

Its also worth noting that although I agree (and am very familiar) with the XP philisophy on this one, I think you'r mis-interpreting it slighty. Considering the timjeline for payback on a code investment now is important. But its also important not to read that as 'do nothing other than the bar minimum'..

One might consider that if he did use the general mechanism, he stays in line with the product evolution, and therefore makes a saving in the long term as he can benefit from additional work others do on improving that mechanism...

That is a reasonable certainly.

Also. Given that the mechanism already exists and is a peice of piss to use, then surely its easier in this case than 'doing your own thing'?

Therefore, as I mentioned earlier, unless there are *real* performance worries or very high volume, perhaps it makes more sense to consider the existing solution...

I  guess I'm not syaing which is best, I just think Branimir is being a bit 'black or white' on this one.

Its also an XP implied-ism, that one should also not re-develop, avoid, functionality just cos its not perfect. If it will do the job, it will do.... refactoring only need come in as and when a problem arises (which it may never)..

I think the discussion of site-wide (ACS object) solutions versus package specific solutions is very interesting and very important. I also think I understand both sides of the discussion. For now I am staying with the mapping table. Why? It's a proven solution that is quick to implement, reliable, and easy to understand and maintain.

I just checked out the the acs-rels data model and it does have some appeal to me. However, the API is a bit involved (you have to create relationship types as well as relationship roles) and it never really caught on with developers it seems - it is hardly used in OpenACS as far as I can tell. It is not clear to me that the toolkit is evolving in the direction of acs-rels.

I think ACS permissions is a good example of how a generic admin UI for ACS objects can be useful. However, in the case of relationships I'm not sure we will ever maintain them with a generic admin UI, let alone generic user UI. The only system that I've seen with such a generic mapping mechanism is ShareNet that lets you navigate knowledge objects of any type and link them together (they are not using acs-rels for this though). I wonder if Rafael Schloming et al ever had something like that in mind for acs-rels.

So to sum up I am staying with the field tested simple mapping table. If there is a good reason to use acs-rels in the future I can write an upgrade script to migrate the mappings there then.

Thanks for all the feedback and good ideas!

I think that's fine, Peter.  I think it underscores our need to provide a simple, lightweight mapping solution in the future.  One advantage of having a relationship type defined as an object is that permissions on types of relationships does make sense (think of a rating package and perhaps the desire to limit an entire class of ratings to, say, professors or students).  One might not know in advance whether or not a future package customizer might want to do that ...

Perhaps there should be a couple of different mapping paradigms available, a heavyweight one like the current acs-rels (while having each row in the map be an object is annoying it does allow for extending the map with additional information) and a lightweight facility for basic mapping.

A major advantage of a centralized lightweight facility lies in maintainability.  If 30 packages each have their own simple mapping tables that means we have 30 table names to remember, 30 (probably slightly different) sets of  PL/SQL procs to add entries to remember (or you have to remember which packages just use "insert", which provide a Tcl API, which provide a PL/[pg]SQL API) etc etc.

Consistency is my main argument for central facilities of this sort.  They certainly shouldn't be so complex and involved that writing 30 different mapping tables and APIs is easier than using the central facility.  acs-rels probably isn't that bad but you get the idea ...