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]
