Professional Web Applications Themes

How to create a tree structured db? - MySQL

Hi all, I'm looking into making a tree structured db which will have several roots and many nodes so it needs to be efficient in querying records. I would also like it to be possible to trace origins. Here is a basic example: Frank Jane | | / \ / \ John Jack Paula Maria | | | / \ / / \ Ed Fred Orietta Marta Renata So if Frank is say (1,1) and Jane (1,2). Then John would be (2,1,1) and Maria (2,2,2). This is I guess, fine for a small structured tree but I doubt it will ...

  1. #1

    Default How to create a tree structured db?

    Hi all,
    I'm looking into making a tree structured db which will have several roots
    and many nodes so it needs to be efficient in querying records.

    I would also like it to be possible to trace origins. Here is a basic
    example:

    Frank Jane
    | |
    / \ / \
    John Jack Paula Maria
    | | |
    / \ / / \
    Ed Fred Orietta Marta Renata


    So if Frank is say (1,1) and Jane (1,2). Then John would be (2,1,1) and
    Maria (2,2,2). This is I guess, fine for a small structured tree but I
    doubt it will be managable on a large scale as tables will be increasingly
    complex and unmanagable.

    Can anyone give me a sollution to this problem? Is there some doentation
    I could read about this?

    I am using: mysql Ver 14.7 Distrib 4.1.20, for pc-linux-gnu (i686) using
    readline 5.1

    An upgrade is feasable if it can solve my problem.

    Many thanks to you all.
    -Ed
    Ed Guest

  2. #2

    Default Re: How to create a tree structured db?

    Ed wrote:
    > Hi all,
    > I'm looking into making a tree structured db which will have several roots
    > and many nodes so it needs to be efficient in querying records.
    >
    > I would also like it to be possible to trace origins. Here is a basic
    > example:
    >
    > Frank Jane
    > | |
    > / \ / \
    > John Jack Paula Maria
    > | | |
    > / \ / / \
    > Ed Fred Orietta Marta Renata
    >
    >
    > So if Frank is say (1,1) and Jane (1,2). Then John would be (2,1,1) and
    > Maria (2,2,2). This is I guess, fine for a small structured tree but I
    > doubt it will be managable on a large scale as tables will be increasingly
    > complex and unmanagable.
    >
    > Can anyone give me a sollution to this problem? Is there some doentation
    > I could read about this?
    >
    > I am using: mysql Ver 14.7 Distrib 4.1.20, for pc-linux-gnu (i686) using
    > readline 5.1
    >
    > An upgrade is feasable if it can solve my problem.
    >
    > Many thanks to you all.
    > -Ed

    Ed,

    Don't worry about a physical position in the tree - in fact, it's a very bad
    thing to worry about. What happens, for instance, if you now add someone above
    Frank? You're whole tree needs to be reworked.

    Rather, just keep track of the parent for each item. So Ed and Fred would have
    John as a parent, and John would have Frank as a parent. At the top, Frank
    would have no one for a parent.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: How to create a tree structured db?

    Ed wrote:
    > Hi all,
    > I'm looking into making a tree structured db which will have several
    > roots and many nodes so it needs to be efficient in querying records.
    >
    > I would also like it to be possible to trace origins. Here is a basic
    > example:
    >
    > Frank Jane
    > | |
    > / \ / \
    > John Jack Paula Maria
    > | | |
    > / \ / / \
    > Ed Fred Orietta Marta Renata
    >
    >
    > So if Frank is say (1,1) and Jane (1,2). Then John would be (2,1,1)
    > and Maria (2,2,2). This is I guess, fine for a small structured tree
    > but I doubt it will be managable on a large scale as tables will be
    > increasingly complex and unmanagable.
    >
    > Can anyone give me a sollution to this problem? Is there some
    > doentation I could read about this?
    >
    > I am using: mysql Ver 14.7 Distrib 4.1.20, for pc-linux-gnu (i686)
    > using readline 5.1
    >
    > An upgrade is feasable if it can solve my problem.
    >
    > Many thanks to you all.
    > -Ed
    Take a look at
    [url]http://dev.mysql.com/tech-resources/articles/hierarchical-data.html[/url]


    Paul Lautman Guest

  4. #4

    Default Re: How to create a tree structured db?

    Ed wrote:
    > Hi all,
    > I'm looking into making a tree structured db which will have several roots
    > and many nodes so it needs to be efficient in querying records.
    >
    > I would also like it to be possible to trace origins. Here is a basic
    > example:
    >
    > Frank Jane
    > | |
    > / \ / \
    > John Jack Paula Maria
    > | | |
    > / \ / / \
    > Ed Fred Orietta Marta Renata
    >
    --snip--

    Thank you both for the pointer to doentation and advice!
    Will read Doc and get cracking.

    Regards,
    -Ed

    Ed Guest

Similar Threads

  1. storing well structured tree data
    By Rik in forum MySQL
    Replies: 0
    Last Post: August 14th, 01:21 PM
  2. Replies: 1
    Last Post: June 12th, 12:07 AM
  3. How to have a flash site structured?
    By arthus webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 1
    Last Post: October 20th, 09:37 PM
  4. Dynamically create controls from a network directory tree
    By Jereme in forum ASP.NET Building Controls
    Replies: 0
    Last Post: September 26th, 03:41 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