469,097 Members | 1,603 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,097 developers. It's quick & easy.

Design phase - dealing w/ many to many relationships

Currently there is an Show Registration applicaiton in Access with 5 back ends, I would like to combine the 5 back ends into one DB using SQL Server.

Background info
5 backends because there are 5 shows they do every year
Some of the tables involved are exhibiotrs, repGroups, booths, dealers and members. the relationship between the exhibitors and repgroups are many to many.

New design
Create a new table called showInfo
The problem that I am running into is that the relationshiop between the exhibitors and/or dealers and the showid can be many to many (not the normal, but does happen). What would be the best way to incorporate consolidating the 5 different databases into one?

Ideally when the applicaiton is ran, the user will be able to select which show information they will be working on. We have determined the user would only be looking at one show at a time.

Again I am just in the design phase and am having troubles setting up the relationships.

Jan 19 '07 #1
1 1743
1,017 Expert 512MB
I donít think it is a problem when user looks at one show at a time you just have to use where condition with ShowID to make sure you recieved data for correct show.
When you have many to many relationship you create intermediate table to have multiple relationships managed. This table should hold only ShowID, ExibitorID , another table should be between show and dealers where you will have ShowID, DealerId.
This way each show can have multiple ExibitorIDs and one ExibitorID can have multiple shows.
To get data from such tables for example named Show, Exibitor, ShowExibitor
[PHP]Select s.ShowName, e.ExibitorName
From Show s
Join ShowExibitor se on s.ShowID = se.ShowID
Join Exibitor e on se.ExibitorID = e.Exibitor_id[/PHP]

The same should be for Dealers.

Good Luck.
Jan 19 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

By using this site, you agree to our Privacy Policy and Terms of Use.