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]
