By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,323 Members | 2,033 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,323 IT Pros & Developers. It's quick & easy.

The Many-to-Many Relationship

P: n/a
Hi,

I'm building a database that, once a year, generates surveys that are emailed
to people in our organization. The recipients of the surveys are managers of
various departments. The emails must list each department manager that reports
to the recipient. For example:

John Doe - Director - Department 10 (recipient of the email)
Reporting Managers:
Jane Doe - Manager - Department 10A
John Q. Public - Manager - Department 10B
etc...

From year to year, the survey recipients, and their reporting managers, don't
change drastically, but they do change.

Here's the table structure I've developed to do this. I would appreciate
your comments, particularily on my use of the Many-To-Many relationship:

Table Fields
SurveyedManagers SurveyedManagerID (Primary key.)
<<Fields for last name, first name, etc.>>
SM_mm_Reports SMmmReportsID (Primary key.)
fkSurveyedManagerID (Foreign key, SurveyedManagers.)
fkReportsID (Foreign key, Reports.)
Reports ReportID (Primary key.)
<<Fields for last name, first name, etc.>>
YearsProcessed YearID (Primary key.)
fldYearProcessed
YP_mm_SMmmRep YPmmSMmmRepID (Primary key.)
fkYearsProcessedID (Foreign key, YearsProcessed.)
fkManMMReportsID (Foreign key, SM_mm_Reports.)

Here's the trick: SurveyedManagers relates to Reports through a intermediate
table called SM_mm_Reports. I use the composite key from this table in the
YP_mm_SMmmRep table as a foreign key. This way, I can get any number of years
of surveys with varying relationships between SurveyedManagers and Reports w/o
repeating data entry in the SurveyedManagers or Reports tables.

Anyway, does this look correct (i.e. - having a many-to-many table relating to
another many-to-many table [SM_mm_Reports to YP_mm_SMmmRep]). The only thing
I'm trying to accomplish is to show relationships between Managers and Reports
over time. Is their an easier way?

I really appreciate any comments on this. My theoritical understanding of
normal data begins to get weak at the mere mention of the many-to-many relation-
ship.

TIA,
Johnny
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Note also that with Jet/Access it is difficult to consistently
get updateable views when using many-many relationships. There
is no solution. You may have to choose to use different display
and update views (or de-normalise and duplicate data).

(david)

"Johnny Meredith" <me**************@msn.com> wrote in message
news:70**************************@posting.google.c om...
Hi,

I'm building a database that, once a year, generates surveys that are emailed to people in our organization. The recipients of the surveys are managers of various departments. The emails must list each department manager that reports to the recipient. For example:

John Doe - Director - Department 10 (recipient of the email)
Reporting Managers:
Jane Doe - Manager - Department 10A
John Q. Public - Manager - Department 10B
etc...

From year to year, the survey recipients, and their reporting managers, don't change drastically, but they do change.

Here's the table structure I've developed to do this. I would appreciate
your comments, particularily on my use of the Many-To-Many relationship:

Table Fields
SurveyedManagers SurveyedManagerID (Primary key.)
<<Fields for last name, first name, etc.>>
SM_mm_Reports SMmmReportsID (Primary key.)
fkSurveyedManagerID (Foreign key, SurveyedManagers.) fkReportsID (Foreign key, Reports.)
Reports ReportID (Primary key.)
<<Fields for last name, first name, etc.>>
YearsProcessed YearID (Primary key.)
fldYearProcessed
YP_mm_SMmmRep YPmmSMmmRepID (Primary key.)
fkYearsProcessedID (Foreign key, YearsProcessed.)
fkManMMReportsID (Foreign key, SM_mm_Reports.)

Here's the trick: SurveyedManagers relates to Reports through a intermediate table called SM_mm_Reports. I use the composite key from this table in the YP_mm_SMmmRep table as a foreign key. This way, I can get any number of years of surveys with varying relationships between SurveyedManagers and Reports w/o repeating data entry in the SurveyedManagers or Reports tables.

Anyway, does this look correct (i.e. - having a many-to-many table relating to another many-to-many table [SM_mm_Reports to YP_mm_SMmmRep]). The only thing I'm trying to accomplish is to show relationships between Managers and Reports over time. Is their an easier way?

I really appreciate any comments on this. My theoritical understanding of
normal data begins to get weak at the mere mention of the many-to-many relation- ship.

TIA,
Johnny

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.