Help with setting up Many to Many relationship

I am pretty new to SQL, but having fun learning at this point. One of the things I am trying to do is setup an asp page here at work that list some of the clients, and the contacts for them.

Most of the sites have many contacts. A few of the contacts have many sites. How would I go about setting this up?

Currently, I have a table setup for sites and one for contacts. One the contacts table, I have a field for "site_id" and just relate it back for the site. This works fine to have multiple contacts associated to a site, but what do I do if the contact needs to be associated to more than one site?

Thanks in advance for the help,
Marcus

You never want to have a many to many relationship.
You should create another table that has a relationship to both of them.

Could you please provide an example?

Post some of your data and you database structure and I will be better able to demonstrate what I mean.

My contacts table consists of the a unique ID for the person, their name, phone #, email address, and the site_id they're associated with on the site table.

The site table consists just of the unique ID for each site, and the site name.

So for an example:

contacts
ID......Name..........phone............email...... ....site ID
1.......person1.......111-111........ 1@1.com ......1

site
site id....... site name
1..............site 1

sorry for all the periods, its the easiest way i could separate them to look like a table format.

Thanks,
Marcus

Table Contacts
ContactID (PK)

Table Site
SiteID (PK)

TableSiteContacts
ContactID (PK/FK from Contacts)
SiteID (PK/FK from Site)


This configuration allows one contact to be the contact for multiple sites and allows one site to have multiple contacts, Thus creating a Many to Many replationship between Contacts and sites.

Many to Many replationship exist only in theory. They can't exist physically (at least not yet)

S-

Thanks for the response...its a bit more clear now.

Im guessing on the SiteContacts table I would have a unique key, and use this to pull info with my SQL statement

Something like:

SELECT *
FROM contacts, site, contactsssite
WHERE contacts.ContactID = contactssite.ContactID
AND site.siteID = contactssite.siteID

....or am I way off here?

=)

Thanks,
Marcus

S-

Thanks again.