relationships in a many to many databse (i think)

[panosD]

I work in a business that creates ads for business and plays them in sporting clubs and pubs and other venues. We are trying to create a database that lets us list the particular sponsers that play at any given venue and also which venues a particular sponser is playing at.

So far I have created a table for the venues and a table for the sponsers but I am stuck there. I have played around with queries and relationships but I don't seem to be able to find a solution for this. So far I have made seperate tables for each of the venues with the sponserID for each of the ads, linking back to a 'sponsors' table i made with an autonumber. Unfortunately this doesn't seem to be enough and I'm not sure where to go from here. My relationships page looks like a spiders web.

Can anyone help me? There can't be any doubles as we need to add and delete sponsers and venues as we need to and for it to update the whole database when we do so.

Thanks
Panos

 

[LittleSmudge]

I thinks what you're after is a classic Many-to-Many configuration :-

tblClubs
ClubsPrimKey
ClubsField2
ClubsField3
.. . etc . ..


tblAdverts
AdvertPrimKey
AdvertField2
AdvertField3
.. . etc . ..


tblWhereWhat
ForeignKeyTotblClubs
ForeignKeyTotblAdverts
OtherFields1
OtherFields2


In the tblClubs you store all the generic info about the Club/Pub/etc

In the tblAdvert you store all the info about individual Adverts

In the tblWhereWhat you uniquely link the location to the advertisement. You might also need things like startDate, EndDate, Links to fee details etc ( anything that changes on a job-by-job basis ) that is not consistent across a particular venue or a particular advert.

IF you only ever play an advert in a location for one session then the two ForeignKey fields could combine as your Primary Key for that table - Else you might need to inclue the StartDate in there as well.


G LS

 

[panosD]

The only information I need to be storing at the moment is the names of the venues and the clubs.

Basically all I need this database to do is, like I said, let us list the particular sponsers that play at any given venue and also which venues a particular sponser is playing at.

Thankyou for your help