Re: Social Networking querys
>i'm not sure who the original poster was I'm Fasani, I work in a spanish telecommunications company on the statistics department as a DBA/developer. I'm on this list because we use mysql for the online statistics and I'm always reading this when I have some free time. Thanks all who commented on this, I 'm researching now because I'm making something different that I want to build on, it's kind of different from friendster. I know that I will face several problems in making what I want to achieve. But mysql is powerfull enough to be the DB engine. I hope that this not become off topic, I will search more on link analisys and social networking, if there is any other thing related that is not regarding mysql please send me to my personal email and not to all the list. Best regards, -- Martin F. www.movil.be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
On Wed, Feb 02, 2005 at 10:55:41AM -0800, Bruce Douglas wrote: > balazs... > > cool... makes sense. then if that's the case, share away how's > the weather over in fl. i noticed on your site that you guys are fairly > small, makes for a good environment when getting work done! One thing I definitely cannot share is the number of users, but I wouldn't say we're small at all. > how does your app compare to ryze or linkedin, regarding the underlying > networking engine... > > ryze appears to be a rather simple/straight-forward list of > lists/links approach, whereas linkedin has the ability to allow msgs to > only proceed between nodes if one is a 'trusted' node within the > chain Honestly I haven't used the sites you mentioned. These are Multiply's capabilities: There are different relationship types (about 50) that describe the relationship with your contacts. Such as father, fraternity brother, co-worker, etc... You can send messages and post content to people in your network described based on the relationship. For example "my family members", "my friends and their friends", you get the idea... The relationship graph engine can tell the shortest (most meaningful) connection or all possible connections between any two people. It can efficiently check if a piece of content is viewable by a user based on what permissions the creator of the content gave. It's very flexible, which is a great thing if you work in a startup and bizdev people throw curveballs at you all the time ;-) Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
We have implemented a system which uses 'swappable' in-memory data, binary trees and hashtables to do things like: o Return 'best' possible route from one person to another based on weight of relationships between links o Return 'shortest' possible route from one person to another o Return total persons within N degrees of seperation and so forth. The in-memory data are flushed when memory is tight or when they get too old, and get replaced with more data from InnoDB tables. Essentially, it is quite rate to need to swap or load db data.given that the data structures are not heavy in memory requirements. Its writen in C++. Our tests with 10 million users (random set) indicated less than 1 second response time for any of the above operations (when no mem-disk swaps take place ). MarkP On Wed, 2 Feb 2005 13:44:52 -0500, Balazs Rauznitz <[EMAIL PROTECTED]> wrote: > On Wed, Feb 02, 2005 at 10:19:45AM -0800, Bruce Douglas wrote: > > dathan... > > > > given that you work at friendster, aren't you kind of restricted from > > commenting on how one would go about creating/implementing this kind of > > system?? > > I thought about this too when describing my company's solution > (http://multiply.com/ just for full disclosure). Considering that > technology is just one of many keys to the success of a company, I > think techies benefit much more from sharing some experiences with > peers rather than keep everything secret and hope that the other guy > screws up. Of course as long as help is reciprocal... > > Balazs > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- Mark Papadakis Head of R&D Phaistos Networks, S.A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
balazs... cool... makes sense. then if that's the case, share away how's the weather over in fl. i noticed on your site that you guys are fairly small, makes for a good environment when getting work done! how does your app compare to ryze or linkedin, regarding the underlying networking engine... ryze appears to be a rather simple/straight-forward list of lists/links approach, whereas linkedin has the ability to allow msgs to only proceed between nodes if one is a 'trusted' node within the chain -bruce -Original Message- From: Balazs Rauznitz <[EMAIL PROTECTED]> Sent: Feb 2, 2005 10:44 AM To: Bruce Douglas <[EMAIL PROTECTED]> Cc: Dathan Pattishall <[EMAIL PROTECTED]>, mysql@lists.mysql.com Subject: Re: Social Networking querys On Wed, Feb 02, 2005 at 10:19:45AM -0800, Bruce Douglas wrote: > dathan... > > given that you work at friendster, aren't you kind of restricted from > commenting on how one would go about creating/implementing this kind of > system?? I thought about this too when describing my company's solution (http://multiply.com/ just for full disclosure). Considering that technology is just one of many keys to the success of a company, I think techies benefit much more from sharing some experiences with peers rather than keep everything secret and hope that the other guy screws up. Of course as long as help is reciprocal... Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Social Networking querys
DVP Dathan Vance Pattishall http://www.friendster.com > -Original Message- > From: Bruce Douglas [mailto:[EMAIL PROTECTED] > Sent: Wednesday, February 02, 2005 10:20 AM > To: Dathan Pattishall > Cc: mysql@lists.mysql.com > Subject: RE: Social Networking querys > > dathan... > > given that you work at friendster, aren't you kind of > restricted from commenting on how one would go about > creating/implementing this kind of system?? We are going to present at the mySQL conference, and let people know how we created the storage engine not how we interact with our API via our storage engine. I could talk to the business guys to see if we can open up the API to allow other companies to use our sauce to run a social network application-but it will require a partnership etc. > > if you aren't and you have information to share, then we'd > appreciate hearing it!!! > > regards, > > bruce > > > -Original Message- > From: Dathan Pattishall <[EMAIL PROTECTED]> > Sent: Feb 2, 2005 10:04 AM > To: Balazs Rauznitz <[EMAIL PROTECTED]>, listsql listsql > <[EMAIL PROTECTED]> > Cc: mysql > Subject: RE: Social Networking querys > > > > DVP > > Dathan Vance Pattishall http://www.friendster.com > > > > > -Original Message- > > From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, February 02, 2005 9:06 AM > > To: listsql listsql > > > I work at a social networking company. We store the social > network as > > an edge graph too, but do not use SQL to run queries on it. It'd be > > just way too slow in huge networks. > > There's a custom application creted in-house written in C > to do that. > > Hope this was at least a little useful. > > > > Also there's somebody from Friendster here, he might be > able to help > > you also. > > We have our own storage engine that talks an API to our own > C++ server that stores the graph. Doing it with a pure myISAM > engine is possible but it's way to slow. Doing a self table > join or subselect is really the only way doing it, less you > want to have some application logic that issues 1000s of selects. > > > > > > Balazs > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
On Wed, Feb 02, 2005 at 10:19:45AM -0800, Bruce Douglas wrote: > dathan... > > given that you work at friendster, aren't you kind of restricted from > commenting on how one would go about creating/implementing this kind of > system?? I thought about this too when describing my company's solution (http://multiply.com/ just for full disclosure). Considering that technology is just one of many keys to the success of a company, I think techies benefit much more from sharing some experiences with peers rather than keep everything secret and hope that the other guy screws up. Of course as long as help is reciprocal... Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Social Networking querys
dathan... given that you work at friendster, aren't you kind of restricted from commenting on how one would go about creating/implementing this kind of system?? if you aren't and you have information to share, then we'd appreciate hearing it!!! regards, bruce -Original Message- From: Dathan Pattishall <[EMAIL PROTECTED]> Sent: Feb 2, 2005 10:04 AM To: Balazs Rauznitz <[EMAIL PROTECTED]>, listsql listsql <[EMAIL PROTECTED]> Cc: mysql Subject: RE: Social Networking querys DVP Dathan Vance Pattishall http://www.friendster.com > -Original Message- > From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] > Sent: Wednesday, February 02, 2005 9:06 AM > To: listsql listsql > I work at a social networking company. We store the social > network as an edge graph too, but do not use SQL to run > queries on it. It'd be just way too slow in huge networks. > There's a custom application creted in-house written in C to > do that. Hope this was at least a little useful. > > Also there's somebody from Friendster here, he might be able > to help you also. We have our own storage engine that talks an API to our own C++ server that stores the graph. Doing it with a pure myISAM engine is possible but it's way to slow. Doing a self table join or subselect is really the only way doing it, less you want to have some application logic that issues 1000s of selects. > > Balazs > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Social Networking querys
DVP Dathan Vance Pattishall http://www.friendster.com > -Original Message- > From: Balazs Rauznitz [mailto:[EMAIL PROTECTED] > Sent: Wednesday, February 02, 2005 9:06 AM > To: listsql listsql > I work at a social networking company. We store the social > network as an edge graph too, but do not use SQL to run > queries on it. It'd be just way too slow in huge networks. > There's a custom application creted in-house written in C to > do that. Hope this was at least a little useful. > > Also there's somebody from Friendster here, he might be able > to help you also. We have our own storage engine that talks an API to our own C++ server that stores the graph. Doing it with a pure myISAM engine is possible but it's way to slow. Doing a self table join or subselect is really the only way doing it, less you want to have some application logic that issues 1000s of selects. > > Balazs > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
nick... just from curiousity.. is the code that you've alluded to available for public play. or is it yours/your company's? thanks bruce -Original Message- From: Nick Arnett <[EMAIL PROTECTED]> Sent: Feb 2, 2005 9:08 AM To: listsql listsql <[EMAIL PROTECTED]> Cc: mysql Subject: Re: Social Networking querys 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 interesting. 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! Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
we're going to need this kind of functionality as we're starting to architect an app that will be a kind of hybrid between a directory listing and ryze/linkedin, so we're going to need the ability to do the foaf/node links. we were actually hoping that someone had already produced a kind of 'open source' algorithm describing/explaining/demonstrating how this process can be implemented!!! yeah.. i know.. but it's nice to dream!! anybody interested in sharing ideas!!! -bruce -Original Message- From: Balazs Rauznitz <[EMAIL PROTECTED]> Sent: Feb 2, 2005 9:33 AM To: Bruce Douglas <[EMAIL PROTECTED]> Cc: listsql listsql <[EMAIL PROTECTED]>, mysql Subject: Re: Social Networking querys On Wed, Feb 02, 2005 at 09:20:28AM -0800, Bruce Douglas wrote: > hi... > > i'm not sure who the original poster was... but, there is also an open source > app (yogurt) that claims to be used to deal/implement a version of a foaf > system, much like okurt/google. i'm not sure how the underlying 'special > sauce' is implemented, but it might be worth checking out... > > let us know what you find. this seems to be the kind of thing that has > probably been described in the comp sci literature as graph theory/edge graph > theory has been around for awhile. also the ability to describe realtionships > between nodes within a graph is a topic that i believe has had a good deal of > research from an academic point of view... Cool. Let us know if you find something interesting. Are you planning to allow different colors for the edges (relationship types) ? I'll "race" any library that's been written :-) Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
On Wed, Feb 02, 2005 at 09:20:28AM -0800, Bruce Douglas wrote: > hi... > > i'm not sure who the original poster was... but, there is also an open source > app (yogurt) that claims to be used to deal/implement a version of a foaf > system, much like okurt/google. i'm not sure how the underlying 'special > sauce' is implemented, but it might be worth checking out... > > let us know what you find. this seems to be the kind of thing that has > probably been described in the comp sci literature as graph theory/edge graph > theory has been around for awhile. also the ability to describe realtionships > between nodes within a graph is a topic that i believe has had a good deal of > research from an academic point of view... Cool. Let us know if you find something interesting. Are you planning to allow different colors for the edges (relationship types) ? I'll "race" any library that's been written :-) Balazs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
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]
Re: Social Networking querys
hi...
i'm not sure who the original poster was... but, there is also an open source
app (yogurt) that claims to be used to deal/implement a version of a foaf
system, much like okurt/google. i'm not sure how the underlying 'special sauce'
is implemented, but it might be worth checking out...
let us know what you find. this seems to be the kind of thing that has probably
been described in the comp sci literature as graph theory/edge graph theory has
been around for awhile. also the ability to describe realtionships between
nodes within a graph is a topic that i believe has had a good deal of research
from an academic point of view...
looks like it's time for a google search!
regards,
bruce
ps. i know, this has only a tangental relationship to mysql!!!
-Original Message-
From: Balazs Rauznitz <[EMAIL PROTECTED]>
Sent: Feb 2, 2005 9:05 AM
To: listsql listsql <[EMAIL PROTECTED]>
Cc: mysql
Subject: Re: Social Networking querys
On Wed, Feb 02, 2005 at 04:47:31PM +0100, 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 ?
I work at a social networking company. We store the social network as
an edge graph too, but do not use SQL to run queries on it. It'd be
just way too slow in huge networks. There's a custom application creted
in-house written in C to do that. Hope this was at least a little
useful.
Also there's somebody from Friendster here, he might be able to help
you also.
Balazs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
Peter Brawley wrote: is, there is some method to iterate in this relation to avoid joining the table in itself each time ? Exactly the problem with trying to model an anything-goes network, a "world of ends", in a relational model of a directed graph. I think you need an XML layer in there, eg see http://iswc2004.semanticweb.org/demos/03/. XML isn't necessary and probably just makes it more complicated. As someone wrote, if you have a problem and decide to solve it with XML, now you have two problems. On the other hand, since the semantic web relies on graphs, approaches that work for it do apply... but unless interoperability matters, it's hard for me to imagine a good reason to bother with XML. Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
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 interesting. 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! Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
On Wed, Feb 02, 2005 at 04:47:31PM +0100, 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 ?
I work at a social networking company. We store the social network as
an edge graph too, but do not use SQL to run queries on it. It'd be
just way too slow in huge networks. There's a custom application creted
in-house written in C to do that. Hope this was at least a little
useful.
Also there's somebody from Friendster here, he might be able to help
you also.
Balazs
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Social Networking querys
is, there is some method to iterate in this relation
to avoid joining the table in itself each time ?
Exactly the problem with trying to model an anything-goes network, a
"world of ends", in a relational model of a directed graph. I think you
need an XML layer in there, eg see
http://iswc2004.semanticweb.org/demos/03/.
PB
-
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,
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.4 - Release Date: 2/1/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Social Networking querys
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")
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
