Shroder/nodemesh — Issue #4: Many to Many Lookup Table Redesign
Author: Shroder
Created: 2011-12-07 03:56:50+00:00
State: open

# Review
The Mesh supports directional linking. It accomplishes this by holding the direction value in the lookup table, in addition to a label. For the direction, our options are LTR, RTL, and BIDI. The set value is relative to the pk1 record in the lookup table. When building the SQL, the left chained node (innerColumn) is used to manipulate the direction (code below).

````php
list($forward, $reverse) = ('pk1' == $innerColumn) ? array('LTR', 'RTL') : array('RTL', 'LTR');
$sql .= ", (CASE WHEN direction = '$forward' THEN 'forward' WHEN direction = '$reverse' THEN 'reverse' ELSE NULL END) AS direction";
````

So if we have this lookup table:
node1#node2
1 | 2 | RTL

And we do an API call of Node1->Node2, the direction remains RTL. However if we use Node2->Node1 it is now LTR.

Note, it is possible to receive a mixture of directions when the relationship is not filtered by direction.

# Issues

I'm proposing changes to the lookup tables to make it easier to make sense of directions from outside the API, and overcome some limitations the current implementation has.

**Direction Manipulation**
The manipulation of the direction requires some processing outside of the query or database. This processing is what PHP does to decide what values to put in the case.

Is there really a need to make the SQL human readable? While the API is meant to hide SQL so that coders interact with the data model in an object-oriented fashion, difficult to read SQL makes debugging and feature additions more challenging. On the other hand, performance is also very important.

In addition, the need to manipulate the direction also prevents the ability to run complex direction based queries. Such as:

Person("rel:follows", "dir:LTR")->Person("dir:RTL", "rel:likes")->Movie

**Limitations**
1.) There is a unique constraint on the lookup table preventing more then one link record, and making it impossible to set more then one relationship between two nodes.

2.) Only one label can be applied to the relationship. I am guessing the label is meant to describe the relationship based on the direction.

# Proposal
Here are two designs to address the issues above. I'm currently undecided on which sounds best, but I'm leaning towards this first one.

**Dividing the Lookup**
The first approach is like the in/out edge concept that the Gremlin video described. There would be potentially two lookup tables for inbound and outbound relationships.

Chain: Node1->Node2
Table Name: Node1#Node2#IN
Columns: fk1 | fk2 | Direction | Label
Values: 1 | 2 | RTL |

Chain: Node2->Node1
TableName: Node2#Node1#OUT
Columns: fk1 | fk2 | Direction | Label
Values: 2 | 1 | LTR |

The beauty of this is the data we are given is exactly what we're looking for, and it's pure/unmanipulated. If I run Node1->Node2, the lookup record is 1|2|RTL. Duplicating the lookup may seem unneeded for a db model with only bidirectional links, but it will be ideal for supporting a model that has multiple relationships and non-bi links.

Pros:
1.) A direction can be natively selected
2.) Directions and labels require no reverse-manipulation. If a reverse relationship exists, it should exist in the "IN" lookup.

There are two downsides to this design:
1.) Extra joins will be needed for reverse-directional lookups.

**All in One**
Alternatively, the lookup table can be modified to have so that each side of the lookup has a direction:
pk1 | pk1_direction | pk1_label | pk2 | pk2_direction | pk2_label
1 | RTL | Following| 2 | LTR | Follower

So now if we're doing Node2->Node1, Node1#Node2.pk1_direction is our direction.

Pros:
1.) A direction can natively be selected

Cons
1.) The query will still need some manipulation to set the field the direction is pulled from