Re: Newbie - restructuring to eliminate many-to-many

I agree, there is nothing wrong with many-to-many. However, the sad fact remains that it is difficult to do well, given all the complex and vague requirements that the real world throws at you, and the vagaries of NULLs in SQL. And there isn't much practical how-to information available, other than manuals that say "just put an association table in between". - cb

JRStern <JRStern_at_gte.net> wrote in message news:3aa035b2.28505158_at_news.gte.net...
> On Fri, 02 Mar 2001 22:11:12 GMT, "in too deep" <fieury_at_hotmail.com>
> wrote:
> >The only way I can see to build the database is with many-to-many
> >relationships, but I fear that will make it much more difficult to code
 (in
> >.asp).
>
> This is like the simplest possible case of many-to-many. Just put an
> "association table" in between with plan and partner fields. There is
> nothing wrong with this, for gosh sakes, it's what relational
> databases are all about!
>
> You then use "joins" in select statements to look up what you want.
>
> Table Plan
> * plankey
> * plan description
> * subject
> * teacher
> * whatever
>
> Table PlanPartner
> * plankey
> * partnerkey
> (the primary key for this table is plankey+partnerkey)
> (this is the "association table")
>
> Table Partner
> * partnerkey
> * partner description
> * address
> * contact
> * whatever
>
> (Also maybe subject/teacher/whatever tables, of course)
>
> select plan.*, partner.*
> from plan
> inner join planpartner
> on plan.plankey=planpartner.plankey
> inner join partner
> on planpartner.partnerkey=partner.partnerkey
> where plan.subject = 'science'
> or
> partner.partnerkey="GEDallas"
>
> etc.
>
> J.
>
Received on Sun Mar 04 2001 - 00:15:56 CET