There are several types of many-many-many tables in my work. I have applications that associate words with other words (ngrams) and databases that can connect users with other users (friends / followers).
I would like to know how I can build relationships with this data in order to find useful connections for my recommendations and for my users.
Take the following simplified database table with a foreign key connecting a node to another node.
id | node1 | node2
--------------------
1 | 1 | 2
2 | 1 | 3
3 | 1 | 4
4 | 2 | 1
5 | 2 | 3
6 | 2 | 5
7 | 3 | 1
For example, in the above table, it may be obvious that “1” is the most popular because it is associated with the majority (2 and 3). It might also seem that perhaps “2” should be associated with “4,” since “2” shares so many relationships with “1” (and “1” is associated with “4”).
So, for example, I could find:
- which connect nodes to other nodes.
- useful affinity-based compounds (recommendations)
- groups of nodes that share related connections
Other common forms of relationships are things like user <=> friendsor blog_post <=> tags.
I'm looking for SQL queries that can be executed directly by the front end, or map shrink jobs that can run every night to pull relationships from this data.