471,573 Members | 1,147 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,573 software developers and data experts.

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 1780
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 Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.