[SQL/V4MD] best way to link one record to multiple categories ?

[Olaf Schmidtmann]

Hi Mathew,

> Thank you for the clarification...makes great sense. In this 
> case I would
> definitely need an additional table for each Many-to-Many 
> relationship that
> exists within the table of items... So say table 'Items' has 3 of its
> columns where there could be M:M relationships to tables 'CategoryA',
> 'CategoryB', and 'CategoryC' - each of those columns would 
> require a table
> to perform the M:M lookup as well, correct? Thus bringing the 
> number of
> in-between tables to 3.... 

Yep if you need that much of n:n connections, I don´t know any better way.
You can put all these cross tables in one table but the result is the same.
But do you really need a table for each category? Can't you store them all
in one table? This would be better, I think and categories are just names,
are they? If you need a hierachy there are other ways to do that.

It´s snowing outside :-)
I am going home now. Good night!

Olaf

> Just seems like it would get messy 
> if you have a
> lot of instances of this in your DB in many different tables, 
> but hopefully
> it could be designed to avoid such problems.



> 
> Thanks,
> Mathew
> 
> ----- Original Message -----
> From: "Olaf Schmidtmann" <os at brainbits.net>
> To: "'Valentina Developers'" <valentina at lists.macserve.net>
> Sent: Thursday, January 30, 2003 1:29 PM
> Subject: RE: [SQL/V4MD] best way to link one record to 
> multiple categories?
> 
> 
> > Hi Mathew,
> >
> > sorry I did not express myself clearly, I guess.
> > I meant that you have categories and items. Both of them 
> are stored one
> > table.
> > So let`s say item 'coke' is in the following categories: 
> 'soft drink'
> (RecID
> > 3), 'rust remover'(RecID 7), 'imperialistic' (RecID 666) ;-) ...
> > The item 'coke' has the ID 3 in table items so your 
> resulting table looks
> > like this:
> >
> > 3 : 3
> > 3 : 7
> > 3 : 666
> >
> > You just need ONE table for that assignment.
> > Got it?
> >
> > bye
> > Olaf
> >
> > > -----Original Message-----
> > > From: Mathew Ray [mailto:mathew at iqtv.com]
> > > Sent: Thursday, January 30, 2003 7:15 PM
> > > To: Valentina Developers
> > > Subject: Re: [SQL/V4MD] best way to link one record to multiple
> > > categories?
> > >
> > >
> > > Thanks Olaf,
> > >
> > > In this example, if I had N categories, the additional table
> > > would need N
> > > columns, correct? Basically it would build a sort of look-up
> > > table for every
> > > record? Wouldn't I also need to index every field so that 
> I can find
> > > associations from any table's record to any other record?
> > >
> > > Thanks,
> > > Mathew
> > >
> > > ----- Original Message -----
> > > From: "Olaf Schmidtmann" <os at brainbits.net>
> > > To: "'Valentina Developers'" <valentina at lists.macserve.net>
> > > Sent: Thursday, January 30, 2003 1:04 PM
> > > Subject: RE: [SQL/V4MD] best way to link one record to
> > > multiple categories?
> > >
> > >
> > > > Hi Mathew,
> > > >
> > > > what about the classic: using an additional table with 
> two fields.
> > > > table_A_RecID | table_B_RecID
> > > > Should do the trick. I'd not advise using lists in fields
> > > and loop them.
> > > > Mrs. Valentina is much faster.
> > > >
> > > > greetinx
> > > > Olaf
> > > >
> > > > --
> > > > Olaf Schmidtmann
> > > > brainbits
> > > > online & cross media
> > > > mailto:os at brainbits.net / www.brainbits.net
> > > >
> > > > > -----Original Message-----
> > > > > From: Mathew Ray [mailto:mathew at iqtv.com]
> > > > > Sent: Thursday, January 30, 2003 6:56 PM
> > > > > To: Valentina Developers
> > > > > Subject: [SQL/V4MD] best way to link one record to multiple
> > > > > categories?
> > > > >
> > > > >
> > > > > I was thinking about this recently and I know it will
> > > > > eventially come up in
> > > > > one of my projects... I've done this in Coldfusion using
> > > > > multiple queries,
> > > > > but I am unsure about the process in Valentina/Director.
> > > > >
> > > > > What is the best way to associate one record to multiple
> > > > > related recIDs in
> > > > > another baseObject? Store a list of recIDs inside the field
> > > > > and then loop
> > > > > through that? Or, is there a way to have some sort of
> > > > > table-within-a-field
> > > > > functionality?
> > > > >
> > > > > Just curious,
> > > > > Mathew
> > > > >
> > > > >
> > > > >
> > > > > _______________________________________________
> > > > > Valentina mailing list
> > > > > Valentina at lists.macserve.net
> > > > > http://listserv.macserve.net/mailman/listinfo/valentina
> > > > >
> > > > _______________________________________________
> > > > Valentina mailing list
> > > > Valentina at lists.macserve.net
> > > > http://listserv.macserve.net/mailman/listinfo/valentina
> > > >
> > >
> > >
> > >
> > > _______________________________________________
> > > Valentina mailing list
> > > Valentina at lists.macserve.net
> > > http://listserv.macserve.net/mailman/listinfo/valentina
> > >
> > _______________________________________________
> > Valentina mailing list
> > Valentina at lists.macserve.net
> > http://listserv.macserve.net/mailman/listinfo/valentina
> 
> 
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://listserv.macserve.net/mailman/listinfo/valentina
>