| 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
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.
----- 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 data.
> 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 identifying
> 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
Unhappiness is discouraged and will be corrected with kitten pictures.