I've been experimenting, again with these. Apart from some bugs, the things are moving. I'll try to summarise my findings and thoughts on this beast called relations.

throw everything into the water

Let's start with a little bit of maths. It gives me that comfort feeling of being serious if I put down some formulas. I might be shooting myself in the head, which is not that comforting.

So, in our case, a relation is a n-tuple - R(X(1),..,X(n)), where Xi are some sets, possibly ordered, or partially ordered sets. The simplest useful relation would be the R(x) - a single value. The equivalent of an SQL databse table is R(Y(1),...,Y(n)), where Y(i) is a column in that table. The equivalent of a SELECT ... WHERE query in this little math markup journey is F(R(Y(1),...,Y(n))), where F() is a filter function implementing the conditions in the where clause. Similarly a row in the db is R(y1,...,yn), where yi is the value for Y(i) in that row.

What is a JOIN? From the SQL books, we find out that a JOIN is the cartesian product of the two relations - J(R1,R2)=R1xR2=Rj(Xr1(1),...,Xr1(n),Xr2(1),...,Xr2(n)), meaning that every row of R1 is combined with every row of R2 and every row of R2 is combined with every row of R1. LEFT and RIGHT JOINs are specialised filters, or syntactic sugar in SQL. We have some conveniece functions, and the possibility to do ordering and grouping of elements, which in our little algebra can be implemented as specialised filter functions. Database VIEWs - another nice syntactic sugar in sql, for remebering a derived relation, but unfortunately we cant use that with mysql.

Bring to boil

So why am I putting all this down? It helps to summarise what do I think I want to achieve with the relations system/api/code/... I working on at the moment.

A node (a bit trimmed) is a relation between the node info(Ni), revision info(Rv), the node type specific info(Nt), and the nodeapi(Na) added one (there is a filter associated with that one, so the re). So a node is Fn(NixNixNtxFa(NixNa)), Fn() simply imposes the requirement of a Nx(nid) being the same. Fa() is nodeapi extension specific, but it follows a similar pattern - the resultant relation of Fa() will always relate to nid one way or another.

A user-node relationship can be defined similarly, either with the aready exhisting ownership or modification history, or by creating a new relation user-node.

Similarly we have other term-node and node-node relations already implemented in book, a taxonomy and a few of the contributed modules.

Stir until you get the required consistency

What if we want to implement a node-node relation, which is mediated by a term (or anything else)?
it becomes node-term-node relation. Let's expand. A term node relation is expressed essentially by Rtn(nid,tid). The question terms related to a node with nid x is Fnt(Rtn|tid|nid==x), ie. from Rtn extract from all rows the tids, where nid is eaqual to x. The nodes related to a term with tid y is Ftn(Rtn|nid|tid==y). So, the node-node relation we are seeking is Rntn(Fnt.Ftn). And all nodes realted to node with nid z, is Fntn(Rntn|Rtn.nid|Rnt.nid=z). In this one we will have implied nesting, because of the sequential composition of the realtions. First Rnt is applied, then using the results, Rtn is applied. The Fntn() = Ftn(RtnxFnt(Rtn|tid|nid==z)|nid|tid in Fnt). My head starts hurting.

Reduce down to thick broth

So we can see that we have two ways of forming new relations - by relation composition and by filter composition. Relation compositions are esentially some filtered cartesian products. Filters can be applied sequentially Fx(Fy(Fz())) or in parallel Fx().Fy().Fz(). Filters are some functions defined on the relations.

Add spices to taste

We need relation descriptors/metadata - basically what is that relation. The minimum we need to have is the information about the database tables - we can get this from the db anyway. Next we need to be able to express and keep information about the composed relations - participating relation, the "basic" relation info and applied "parallel" filters. We need to be able to express as well the "sequential" composition of filters. If we have that we will be able to express a very wide range of relations between things in drupal.

Eat while warm

It is unfeasible to use SQL to express these - with VIEWs yes, problem solved, but otherwise? We need to implement our own VIEW-like system, so we can freely manipulate pre-existing views to create new ones. I would choose either expressing it directly in php, storing the views in db and using eval() calls to execute it, or implementing a parser for a minimal language to represent the described system. We will probably need both, the eval() code being a kind of a cache, I suppose.

Ouch, I forgot! Give it time to rest

That is I'm working on implementing this broth, but your opinion is welcome. After sampling this broth cooked. I think of turning to the structured main course with tree bark gravy.