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

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.

Dave,

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
(http://www.amazon.com/exec/obidos/tg/detail/-/0201616416/103-1935466-0960636)
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.

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 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 ...