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

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 Fri Mar 02 2001 - 18:12:08 CST