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 - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
