List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:February 2 2005 5:33pm
Subject:Re: Social Networking querys
View as plain text  
During a lengthy stint at a major provider of financial data, I came
across this concept generic modelling..  The idea was to have one tables
of 'things' and a second one of 'relationships'..  In concept, it's a
very powerful and elegant idea, but it tends to be recursion-heavy. 
Depending on what you want to do with it and the expected load it might
not be feasible for real-world use.

I think your model is quite elegant and I doubt that XML will improve
the robustness, but bear in mind the Kevin Bacon Postulate:  given a
population of 7 billion everyone is related to everyone else (many
several times) by the time you are down to the 6th degree.  The
efficiency has an exponential inverse relationship to the degree.


 - michael dykman

On Wed, 2005-02-02 at 10:47, listsql listsql wrote:
> Since I read about Foaf [ http://www.foaf-project.org/ ], I become
> interested with Social Networking, and readed a lot about it.
> I 've been playing as well with mysql and join querys to represent
> network's of people. As I made some queries in google and didn't came
> with a lot interesting info about this, now I will comment here my own
> querys.
> 
> urelation table:  (this kind of relation is called the adjacency list
> model, or recursion)
> +-----+-----+
> | uid | fid |
> +-----+-----+
> |   1 |   2 |
> |   2 |   3 |
> |   1 |   3 |
> |   3 |   2 |
> |   3 |   0 |
> |   0 |   2 |
> |   3 |   1 |
> +-----+-----+
> This represent's the id of  people and the id of their friend ('s)
> 
> uprofile table: 
> +-----+-----------+
> | uid | name      |
> +-----+-----------+
> |   0 | martin 0  |
> |   1 | pedro 1   |
> |   2 | pablo 2   |
> |   3 | matias 3  |
> |   4 | mateo 4   |
> |   5 | claudio 5 |
> +-----+-----------+
> 
> 
> 
> So if I want to get the friend's and friend's of friend's  of  pablo:
> 
> SELECT p1.name  p1,p2.name  p2
> FROM uprofile p1,uprofile p2
> left join urelation r1 ON r1.fid=p1.uid 
> and r1.uid =2 
> left join urelation r2 ON r2.fid=p2.uid 
> where r2.uid =r1.fid 
> 
> +----------+----------+
> | p1       | p2       |
> +----------+----------+
> | matias 3 | martin 0 |
> | matias 3 | pedro 1  |
> | matias 3 | pablo 2  |
> +----------+----------+
> 
> And I add logically one join more if I want to get deeper in the network.
> The obvious problem is that for the first table p1 I will get the
> repeating Id, but that is not an issue now.
> 
> Where I wanted to get more feedback is, there is some method to
> iterate in this relation to avoid joining the table in itself each
> time ?
> Or also:
> Is this the best way to store human-like social relations, or there is
> a better way to do this ?
> 
> Thanks in advance,
> -- 
> Martin
>  ("Welcome to saving hierarchical data in mysql: Recursion until your
> head explodes")
-- 
 - michael dykman
 - michael@stripped

Thread
Social Networking queryslistsql listsql2 Feb
  • Re: Social Networking querysPeter Brawley2 Feb
    • Re: Social Networking querysNick Arnett2 Feb
  • Re: Social Networking querysBalazs Rauznitz2 Feb
  • Re: Social Networking querysNick Arnett2 Feb
  • Re: Social Networking querysMichael Dykman2 Feb
Re: Social Networking querysBruce Douglas2 Feb
  • Re: Social Networking querysBalazs Rauznitz2 Feb
Re: Social Networking querysBruce Douglas2 Feb
Re: Social Networking querysBruce Douglas2 Feb
RE: Social Networking querysDathan Pattishall2 Feb
RE: Social Networking querysBruce Douglas2 Feb
  • Re: Social Networking querysBalazs Rauznitz2 Feb
    • Re: Social Networking querysMark Papadakis2 Feb
RE: Social Networking querysDathan Pattishall2 Feb
Re: Social Networking querysBruce Douglas2 Feb
  • Re: Social Networking querysBalazs Rauznitz2 Feb
Re: Social Networking queryslistsql listsql2 Feb