Professional Web Applications Themes

graph network structure with relational DB - MySQL

I have a quite standard graph represention of a social network : an auto-referential 'users' table (user_id , data...) and a 'relationship' join table (from_id, to_id) is there any place to look for information about queries performance (and related tricks or suggestions) when managing such kind of related info , like 'find/count all related people to someone... up to a-level" I read some interessing articles , but all are very academical , math-oriented, far from DB structure... thanks for your advices joss...

  1. #1

    Default graph network structure with relational DB

    I have a quite standard graph represention of a social network :
    an auto-referential 'users' table (user_id , data...) and a
    'relationship' join table (from_id, to_id)

    is there any place to look for information about queries performance
    (and related tricks or suggestions) when managing such kind of related
    info , like 'find/count all related people to someone... up to a-level"
    I read some interessing articles , but all are very academical ,
    math-oriented, far from DB structure...

    thanks for your advices

    joss

    Josselin Guest

  2. #2

    Default Re: graph network structure with relational DB

    I have looked at doing large relational databases for a couple ideas
    that I have had for some social network style projects, but never could
    fully come to a method that I would be happy with.

    Since DB's have a one to one, one to many, and many to one relation
    that would be just about all we could work with.

    With social networking theory you break them in groups, nodes,
    branches, and hubs. That said you would have to have a raw database
    with each users one to one relation(s). From that you could query your
    entire DB to find groups, and hubs then build a 2nd DB with these
    hubs/groups and their members (Nodes I think).

    That way you could say something in the effect of person/item (a) is
    part of or has a one to one relation to (x) hub/group, and person (b)
    has one relation to (z) hub/group, and group (z) is connected to group
    (x), by group (c)>(d)>(m).

    Thus you would only have to find the two users closes hub(s) and then
    query the database step by step to the next hub(less queries).

    Mapping logic... If anyone else has any information on the subject I
    would also like to hear what they have to say on it.

    l3vi Guest

  3. #3

    Default Re: graph network structure with relational DB

    On 2007-01-13 18:41:02 +0100, "l3vi" <com> said:
     

    thanks a lot for your feedback... right now I'll work on 2 levels only
    , and doing simple queries

    a user A has 1st level relationship (let's say 30 other users)
    at 2nd level this 30 people may also have their own 1st level
    relationship, which gives A a second level relationship of 900 users..
    that's all for now... I'll write the methods to handle that

    I read an article (pdf) "Using Structure Indices for Efficient
    Approximation of Network Properties" written by Matthew J. Rattigan
    (umass.edu), Marc Maier (umass.edu) and David
    Jensen (umass.edu) which is very interesting on the subject...

    joss


    Josselin Guest

  4. #4

    Default Re: graph network structure with relational DB

    On 15.01.2007 12:49, Josselin wrote: 

    You can deal with fixed level of nesting queries in a single SELECT
    statement - SQL just chokes on the unlimited recursion (unless you are
    on Oracle and can use CONNECT BY).

    Kind regards

    robert
    Robert Guest

  5. #5

    Default Re: graph network structure with relational DB

    On 2007-01-15 13:12:55 +0100, Robert Klemme <com> said:
     
    >
    > You can deal with fixed level of nesting queries in a single SELECT
    > statement - SQL just chokes on the unlimited recursion (unless you are
    > on Oracle and can use CONNECT BY).
    >
    > Kind regards
    >
    > robert[/ref]

    thanks a lot, I'll
    joss

    Josselin Guest

Similar Threads

  1. Replies: 1
    Last Post: June 6th, 10:38 AM
  2. Replies: 1
    Last Post: May 1st, 03:24 PM
  3. Replies: 2
    Last Post: January 24th, 04:32 PM
  4. GD::Graph - how to get rid of leading gap in line graph?
    By carcassone_fr@yahoo.com in forum PERL Modules
    Replies: 0
    Last Post: October 1st, 04:56 PM
  5. GD::Graph: "mixed" graph doesn't recognize "area" graph type
    By Emilio in forum PERL Miscellaneous
    Replies: 5
    Last Post: October 8th, 02:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139