SEO

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`,`Cat alogCategoryActive`),
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`,`Cat alogCategoryActive`),
KEY `CatalogsCategoriesIndexB`
(`
CatalogCategoryLevel1
`
,`
CatalogCategoryLevel2`,`CatalogCategoryLevel3`,`Ca talogCategoryLevel4`),
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.

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
<brunomagalhaes@blackbean.com.br> 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 eem
> 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 suitablefor
> 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`,`CatalogCategoryModifi edBy`,`CatalogCategoryRemovedBy`),
> > KEY `CatalogsCategoriesIndexC`
> (`CatalogCategoryCreatedOn`,`CatalogCategoryModifi edOn`,`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`,`Cat alogCategoryActive`),
> > KEY `CatalogsCategoriesIndexB`
> (`CatalogCategoryLeftPosition`,`CatalogCategoryRig htPosition`),
> > KEY `CatalogsCategoriesIndexC`
> (`CatalogCategoryCreatedBy`,`CatalogCategoryModifi edBy`,`CatalogCategoryRemovedBy`),
> > KEY `CatalogsCategoriesIndexD`
> (`CatalogCategoryCreatedOn`,`CatalogCategoryModifi edOn`,`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`,`Cat alogCategoryActive`),
> > KEY `CatalogsCategoriesIndexB`
> (`CatalogCategoryLevel1`,`CatalogCategoryLevel2`,` CatalogCategoryLevel3`,`CatalogCategoryLevel4`),
> > KEY `CatalogsCategoriesIndexC`
> (`CatalogCategoryCreatedBy`,`CatalogCategoryModifi edBy`,`CatalogCategoryRemovedBy`),
> > KEY `CatalogsCategoriesIndexD`
> (`CatalogCategoryCreatedOn`,`CatalogCategoryModifi edOn`,`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 eem
> 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 messageor
> 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
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=baron@xaprb.com
>
>



--

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/

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
>