Professional Web Applications Themes

Storing hierarchical data in relational database - MySQL

Is there a proper way to store a hierarchy in a relational database? Something like: primaryKey | nodeName | parentNodePrimaryKey | nodeLevel I could then ask for everything ordered by nodeLevel so I could read it into a data structure in my program, starting at the root level and working my way down (so that when adding a node it's parent would always exist already). However I'd also need to efficiently (it would happen often) ask for a node and only it's parent nodes, can I do this with mysql without having to get 'parentNodePrimaryKey' and make anotehr query to ...

  1. #1

    Default Storing hierarchical data in relational database

    Is there a proper way to store a hierarchy in a relational database?
    Something like:

    primaryKey | nodeName | parentNodePrimaryKey | nodeLevel

    I could then ask for everything ordered by nodeLevel so I could read it
    into a data structure in my program, starting at the root level and
    working my way down (so that when adding a node it's parent would
    always exist already). However I'd also need to efficiently (it would
    happen often) ask for a node and only it's parent nodes, can I do this
    with mysql without having to get 'parentNodePrimaryKey' and make
    anotehr query to get the parent node for the next level up, use it for
    another query, etc, etc?

    hobosalesman@gmail.com Guest

  2. #2

    Default Re: Storing hierarchical data in relational database

    Il 29 May 2006 00:07:56 -0700, [email]hobosalesman[/email] ha scritto:
    > Is there a proper way to store a hierarchy in a relational database?
    > Something like:
    >
    > primaryKey | nodeName | parentNodePrimaryKey | nodeLevel
    >
    > I could then ask for everything ordered by nodeLevel so I could read it
    > into a data structure in my program, starting at the root level and
    > working my way down (so that when adding a node it's parent would
    > always exist already). However I'd also need to efficiently (it would
    > happen often) ask for a node and only it's parent nodes, can I do this
    > with mysql without having to get 'parentNodePrimaryKey' and make
    > anotehr query to get the parent node for the next level up, use it for
    > another query, etc, etc?
    You can have a look here:

    [url]http://dev.mysql.com/tech-resources/articles/hierarchical-data.html[/url]

    --
    Best regards,

    Giuseppe Bogetti
    B.G. Soft
    Cuneo - Italy
    Giuseppe Bogetti Guest

  3. #3

    Default Re: Storing hierarchical data in relational database

    Giuseppe Bogetti wrote:
    > You can have a look here:
    >
    > [url]http://dev.mysql.com/tech-resources/articles/hierarchical-data.html[/url]
    Awesome, that's perfect, thanks.

    hobosalesman@gmail.com Guest

  4. #4

    Default Re: Storing hierarchical data in relational database

    Giuseppe Bogetti wrote:
    > You can have a look here:
    >
    > [url]http://dev.mysql.com/tech-resources/articles/hierarchical-data.html[/url]
    Awesome, that's perfect, thanks.

    hobosalesman@gmail.com Guest

Similar Threads

  1. XML and Relational Data??
    By Gerry_uk in forum PERL Modules
    Replies: 0
    Last Post: June 14th, 08:09 PM
  2. Hierarchical data in datagrid
    By Nicolas Martinez in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: October 23rd, 01:33 AM
  3. Import Data from flat-database to relational-database
    By Dieter Schmidt in forum FileMaker
    Replies: 1
    Last Post: September 17th, 12:05 PM
  4. Replies: 2
    Last Post: September 16th, 10:30 AM
  5. Building relational MySQL database
    By Joe Shockey in forum Macromedia Dreamweaver
    Replies: 2
    Last Post: July 16th, 02:10 PM

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