Many-to-many relationship sharing one common table - possible?

[JaceofSpace]

Hello there,
I'm quite familiar with MS Access but I am not sure how to approach a design problem I have. I'll break down what I have, scenarios I have tried:

The design wording is as follows:
A COUNTRY can have multiple REPORTS and SUBMISSIONS, both REPORTS and SUBMISSIONS can have multiple REFERENCES. (This is just a small example, I actually have 12 tables that link to the COUNTRY table and each can have it's own multiple references).



So my table design so far is:
COUNTRIES
pkID

REPORTS
pkReportID
fkCountryID

SUBMISSIONS
pkSubmissionID
fkCountryID

REFERENCES
pkReferenceID

I was able to setup the one-many relationship of the COUNTRIES and the REPORTS and SUBMISSIONS table but I can't figure out how to get the unique identifier from the REPORTS and SUBMISSIONS to share the REFERENCES table.

I tried setting up a many-to-many relationship with a new table but even then when you add more than one table to that relationship the REPORTS and SUBMISSIONS pkID's are in conflict since each of those tables will have a sequential ID of 1,2,3,4.. etc.

I thought maybe I could change the primary key seed format but again I would have to do that for 12 tables, is that a way to go?

Short of creating the same REFERENCES table for both the SUBMISSIONS and REPORTS table, is there some way I can only use one REFERENCES table and have a one-to-many relationship with multiple tables?


I hope this is not confusing but any help would be much appreciated.

Thanks!!!
J

[June7]

You have 12 tables that can each have multiple associated References?

Sounds like need 12 junction tables.

Very odd.

Why 12 tables? Are the structures (fields) really different?

[JaceofSpace]

Yes exactly - For each table, each can have multiple associated references.

The reason I have 12 tables is that each country may have more than one of each dataset so for this example, each country can have more than one REPORT and/or SUBMISSION and each of those has it's own reference(s). Naturally by saying that, a country may have 3 reports and 1 submission but are not in anyway linked.

So I guess I'll have to create 12 junction tables then?

Thanks!!!

[June7]

Still not understanding what the 12 tables are for. Reports and Submissions account for 2. What are the others? Are the fields in Reports and Submissions the same?

[JaceofSpace]

Ok let me clarify - The 12 tables are for other information that is not the same, the fields in Reports and Submissions are not the same and neither are any of the fields in the other tables. They will all contain very different information relating to each country, therefore they cannot be combined into one table.

Here is a basic example of 5 tables:
COUNTRIES
pkID
Languages
Authority

REPORTS
pkReportID
fkCountryID
Report_Name
Report_Date

SUBMISSIONS
pkSubmissionID
fkCountryID
Submission_Requirements
Submission_Date

AMENDMENTS
pkAmendID
fkCountryID
Amendment_Document
Process

DEVIATION_REPORTING
pkDeviationID
fkCountryID
Protocol
Oversight_Body
Name
Rating

The REFERENCES table I have created - those fields would be standard across all of the tables above, this is where my problem comes in as I wanted to only have 1 table that collected all of the references per table, but there was no way to uniquely identify each of the records.

REFERENCES
pkReferenceID
Collected_Date
Name
Confidence_Index

Does that help?
Thanks!

J

[June7]

Yes, and the only alternative I can envision would be very different data structure and likely not suit your business process and not as user friendly for data entry.

[JaceofSpace]

Ok thanks so much for the help, I'll just create the junction tables as is to keep the structure easy.

J