Just correcting my self,

The hierarchical storage theory is "nested set model", and not
"transverse". And also I know that oracle 10g has a hierarchical
query, but I am more interested on pure SQL2 theories and solutions.
And also, SHOULD (or could) MySQL have some type or recursive query?
For example:

SELECT RECURSIVE *
FROM ProductsCategories
USING ProductCategoryParentID = ProductCategoryID

Is this syntax factive?

Best regards,
Bruno B. B. Magalhães

BLACKBEAN CONSULTORIA
Rua Real Grandeza 193/210, Botafogo
Rio de Janeiro, RJ, 22281-035, Brasil

+55 (21) 9996-1093
+55 (21) 2266-0597
www.blackbean.com.br

Esta mensagem pode conter informação confidencial e/ou privilegiada.
Se você não for o destinatário ou a pessoa autorizada a receber esta
mensagem, não pode usar, copiar ou divulgar as informações nela
contidas ou tomar qualquer ação baseada nessas informações. Se você
recebeu esta mensagem por engano, por favor avise imediatamente o
remetente, respondendo o e-mail e em seguida apague-o. Agradecemos sua
cooperação.

This message may contain confidential and/or privileged information.
If you are not the addressee or authorized to receive this for the
addressee, you must not use, copy, disclose or take any action based
on this message or any information herein. If you have received this
message in error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.

On Apr 29, 2008, at 1:21 PM, Bruno B B Magalhães wrote:

> Hi everybody,
>
> I would like to discuss there hierarchical storage theory. Currently
> I have came across 2 types for storing hierarchical data (yes, I´ve
> read the article at mysql.com, and MANY others sites), but I would
> like to know your option about the day-to-day usage... I mean I´ve
> only used hierarchy for some small to medium projects, but I now I
> am planning a big account project (yes, I´ve researched many open-
> source solutions, but none was suitable for an enterprise level
> solution), and if someone has already walked this path... Well, I
> would like to share and discuss... How Oracle, SAP and Microsoft
> Dynamics did? Some one has some insight? ow they manage to store
> UNSPSC taxonomy data on their systems?
>
> Here is a table using parent and child relationship:
>
> CREATE TABLE `CatalogsCategories` (
> `CustomerID` int(8) unsigned NOT NULL default '0',
> `CatalogID` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryParentID` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryID` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryCode` varchar(20) NOT NULL default '',
> `CatalogCategoryName` varchar(200) NOT NULL default '',
> `CatalogCategoryDescription` text character set latin1 NOT NULL,
> `CatalogCategoryActive` int(1) unsigned NOT NULL default '0',
> `CatalogCategoryCreatedBy` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryCreatedOn` int(20) unsigned NOT NULL default '0',
> `CatalogCategoryModifiedBy` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryModifiedOn` int(20) unsigned NOT NULL default '0',
> `CatalogCategoryRemovedBy` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryRemovedOn` int(20) unsigned NOT NULL default '0',
> KEY `CatalogsCategoriesIndexA`
> (`CustomerID`,`CatalogID`,`CatalogCategoryParentID
> `,`CatalogCategoryID`,`CatalogCategoryActive`),
> KEY `CatalogsCategoriesIndexB`
> (`
> CatalogCategoryCreatedBy
> `,`CatalogCategoryModifiedBy`,`CatalogCategoryRemo vedBy`),
> KEY `CatalogsCategoriesIndexC`
> (`
> CatalogCategoryCreatedOn
> `,`CatalogCategoryModifiedOn`,`CatalogCategoryRemo vedOn`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
>
> Pros: Easy to understand and implement the operating clas es, not
> export / inport friendly
> Cons: Recursive behavior, not suited for reports generation because
> of the recursivity
>
>
> Here is a table using transverse relationship:
>
> CREATE TABLE `CatalogsCategories` (
> `CustomerID` int(8) unsigned NOT NULL default '0',
> `CatalogID` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryID` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryLeftPosition` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryRightPosition` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryCode` varchar(20) NOT NULL default '',
> `CatalogCategoryName` varchar(200) NOT NULL default '',
> `CatalogCategoryDescription` text character set latin1 NOT NULL,
> `CatalogCategoryActive` int(1) unsigned NOT NULL default '0',
> `CatalogCategoryCreatedBy` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryCreatedOn` int(20) unsigned NOT NULL default '0',
> `CatalogCategoryModifiedBy` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryModifiedOn` int(20) unsigned NOT NULL default '0',
> `CatalogCategoryRemovedBy` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryRemovedOn` int(20) unsigned NOT NULL default '0',
> KEY `CatalogsCategoriesIndexA`
> (`
> CustomerID`,`CatalogID`,`CatalogCategoryID`,`Catal ogCategoryActive`),
> KEY `CatalogsCategoriesIndexB`
> (`CatalogCategoryLeftPosition`,`CatalogCategoryRig htPosition`),
> KEY `CatalogsCategoriesIndexC`
> (`
> CatalogCategoryCreatedBy
> `,`CatalogCategoryModifiedBy`,`CatalogCategoryRemo vedBy`),
> KEY `CatalogsCategoriesIndexD`
> (`
> CatalogCategoryCreatedOn
> `,`CatalogCategoryModifiedOn`,`CatalogCategoryRemo vedOn`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
>
> Pros: SQL friendly so much less processing and queries required, not
> export / inport friendly
> Coms: Not that easy to understand or implement the operating classes
>
> Here is a table using UNSPSC like relationship:
>
> CREATE TABLE `CatalogsCategories` (
> `CustomerID` int(8) unsigned NOT NULL default '0',
> `CatalogID` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryID` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryLevel1` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryLevel2` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryLevel3` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryLevel4` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryCode` varchar(20) NOT NULL default '',
> `CatalogCategoryName` varchar(200) NOT NULL default '',
> `CatalogCategoryDescription` text character set latin1 NOT NULL,
> `CatalogCategoryActive` int(1) unsigned NOT NULL default '0',
> `CatalogCategoryCreatedBy` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryCreatedOn` int(20) unsigned NOT NULL default '0',
> `CatalogCategoryModifiedBy` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryModifiedOn` int(20) unsigned NOT NULL default '0',
> `CatalogCategoryRemovedBy` int(8) unsigned NOT NULL default '0',
> `CatalogCategoryRemovedOn` int(20) unsigned NOT NULL default '0',
> KEY `CatalogsCategoriesIndexA`
> (`
> CustomerID`,`CatalogID`,`CatalogCategoryID`,`Catal ogCategoryActive`),
> KEY `CatalogsCategoriesIndexB`
> (`
> CatalogCategoryLevel1
> `
> ,`
> CatalogCategoryLevel2
> `,`CatalogCategoryLevel3`,`CatalogCategoryLevel4`) ,
> KEY `CatalogsCategoriesIndexC`
> (`
> CatalogCategoryCreatedBy
> `,`CatalogCategoryModifiedBy`,`CatalogCategoryRemo vedBy`),
> KEY `CatalogsCategoriesIndexD`
> (`
> CatalogCategoryCreatedOn
> `,`CatalogCategoryModifiedOn`,`CatalogCategoryRemo vedOn`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
>
> Pros: SQL friendly so much less processing and queries required,
> very export / inport friendly
> Cons: Fixed deep and a little bit more difficult to implement the
> operating classes than parent/child relationships
>
> I would like very much to hear your opinions!
>
> Best regards to you all,
> Bruno B. B. Magalhães
>
> BLACKBEAN CONSULTORIA
> Rua Real Grandeza 193/210, Botafogo
> Rio de Janeiro, RJ, 22281-035, Brasil
>
> +55 (21) 9996-1093
> +55 (21) 2266-0597
> www.blackbean.com.br
>
> Esta mensagem pode conter informação confidencial e/ou privilegiada.
> Se você não for o destinatário ou a pessoa autorizada a receber esta
> mensagem, não pode usar, copiar ou divulgar as informações nela
> contidas ou tomar qualquer ação baseada nessas informações. Se você
> recebeu esta mensagem por engano, por favor avise imediatamente o
> remetente, respondendo o e-mail e em seguida apague-o. Agradecemos
> sua cooperação.
>
> This message may contain confidential and/or privileged information.
> If you are not the addressee or authorized to receive this for the
> addressee, you must not use, copy, disclose or take any action based
> on this message or any information herein. If you have received this
> message in error, please advise the sender immediately by reply e-
> mail and delete this message. Thank you for your cooperation.
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=b...ackbean.com.br
>