On Mon, Nov 25, 2013 at 4:29 AM, Johan De Meersman <vegiv...@tuxera.be>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 x/y/z. > > ----- Original Message ----- > > From: "Larry Martell" <larry.mart...@gmail.com> > > To: "Steven Siebert" <smsi...@gmail.com> > > Cc: "mysql mailing list" <mysql@lists.mysql.com> > > 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 > > rows. > > > > -- > Unhappiness is discouraged and will be corrected with kitten pictures. >
