Bruno, Interesting emails. You might consider talking to Arjen Lentz about his special hierarchical-storage magic. It's still in development, but ... well, I'll let you talk with him about it.
Best, Baron On Tue, Apr 29, 2008 at 2:12 PM, Bruno B B Magalhães <[EMAIL PROTECTED]> wrote: > 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`,`CatalogCategoryRemovedBy`), > > KEY `CatalogsCategoriesIndexC` > (`CatalogCategoryCreatedOn`,`CatalogCategoryModifiedOn`,`CatalogCategoryRemovedOn`) > > ) 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`,`CatalogCategoryActive`), > > KEY `CatalogsCategoriesIndexB` > (`CatalogCategoryLeftPosition`,`CatalogCategoryRightPosition`), > > KEY `CatalogsCategoriesIndexC` > (`CatalogCategoryCreatedBy`,`CatalogCategoryModifiedBy`,`CatalogCategoryRemovedBy`), > > KEY `CatalogsCategoriesIndexD` > (`CatalogCategoryCreatedOn`,`CatalogCategoryModifiedOn`,`CatalogCategoryRemovedOn`) > > ) 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`,`CatalogCategoryActive`), > > KEY `CatalogsCategoriesIndexB` > (`CatalogCategoryLevel1`,`CatalogCategoryLevel2`,`CatalogCategoryLevel3`,`CatalogCategoryLevel4`), > > KEY `CatalogsCategoriesIndexC` > (`CatalogCategoryCreatedBy`,`CatalogCategoryModifiedBy`,`CatalogCategoryRemovedBy`), > > KEY `CatalogsCategoriesIndexD` > (`CatalogCategoryCreatedOn`,`CatalogCategoryModifiedOn`,`CatalogCategoryRemovedOn`) > > ) 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/[EMAIL PROTECTED] > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- Baron Schwartz, Senior Consultant, Percona Inc. Tel: +1 888 401 3401 ext 507 24/7 Emergency Line +1 888 401 3401 ext 911 Our Services: http://www.percona.com/services.html Our Blog: http://www.mysqlperformanceblog.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
