listsql listsql wrote:
>Since I read about Foaf [ http://www.foaf-project.org/ ], I become
>interested with Social Networking,
What you're doing is often called link analysis -- searches on that term
may yield more for you to chew on. There are software tools and
visualization tools for answering the kind of questions this data
covers. For the latter, Pajek
(http://vlado.fmf.uni-lj.si/pub/networks/pajek/) is one of the most
More generally, you're storing a graph in a relational database.
Searching on that subject will show you several approaches.
The way I deal with this is to create tables pretty much as you have,
but instead of self-joins in MySQL, I load it all into a program that
stores the graph in hashes, then uses recursion to get the kind of
answers you want. This is much, much, much faster than doing the same
in the database, though it might become memory constrained if you have a
big network. In that case, my next step is to do all of the recursions
and store the results in the database as pairs and their distances from
one another. Then it's a simple lookup.
If you do find a way to do this efficiently in MySQL, I'll be interested!