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
