On Mon, Nov 25, 2013 at 4:29 AM, Johan De Meersman <vegivamp@stripped>wrote:
> | Nodes |
> | id | ---
> | [more fields] | |
> | parent_id | >--
> And then you join the table with itself as needed. Do note that you'll
> need a self-join for every level you query, and that every self-join
> incrementally slows down the query.
> It may be beneficial (actually, I'm pretty sure it will be :-) ) to
> implement the recursion in software as necessary, simply follow the branch
> you want and check wether there are children at each turn; or pick up the
> child and go back up until parent_id is NULL.
> Depending on what you want/need, you could also add a depth field, so you
> don't have to loop to figure out where you are in the tree, etc.
> Storing node x/y/z as an identifier, as you suggest, is also an option
> with it's own benefits and downsides; nothing prevents you from taking any
> or all of those approaches at the same time.
Thanks for the reply. I've had very bad performance in the past with
multiple self joins on large tables. I think I will end up going with using
> ----- Original Message -----
> > From: "Larry Martell" <larry.martell@stripped>
> > To: "Steven Siebert" <smsiebe@stripped>
> > Cc: "mysql mailing list" <mysql@stripped>
> > Sent: Friday, 22 November, 2013 3:17:44 PM
> > Subject: Re: XML to RDB
> > Yes, I will need to query the data, and yes, it's app specific to the
> > The parent-node structure will meet my needs if I can figure out how to
> > preserver the unbound multi level nodes. There will be a higher level
> > table, which will have the file name, date received, and other
> > info that will be start of the query. The nodes will have a foreign key
> > back to the file table. The queries will be something like "get the abcd
> > parameter from node x/y/z from the file foo.xml from 10/10/13". I guess I
> > can just store the x/y/z in the node table (as opposed to trying to
> > represent z is child of y which is a child of x in 3 different tables or
> > rows.
> Unhappiness is discouraged and will be corrected with kitten pictures.