Multiple many-to-many?

[pmregan]

Hi all,

I'm rather new to Access, so I hope I can phrase my use case properly.



I'm trying to build a simple requirements verification database, but I'm getting stuck when I try to set up my relationships between tables.

I start with a table of customer requirements. Each requirement can have multiple compliance activities (e.g. test, analysis, inspection), and each compliance activity can have multiple requirements. This is easy enough with three tables ("requirements," "compliance," and a junction table).

Here's where I get tripped up: each compliance activity is defined in one or more documents, which I have within a "documents" table. For example, I might have a "Mechanical Test" document, and an "Electrical Test" document. Going one step further, each document is comprised of multiple procedures, which I have (all; regardless of the final document where they'll wind up) in a "procedures" table.

Can anyone help me define the relationships between these five tables? I'm getting totally lost with the many-to-many sides of things despite my best efforts to map stuff out! Do I need additional junction tables?

Here's what I have set up so far:

requirementsTBL
requirementID (PK)
requirement_text

complianceTBL
complianceID (PK)
compliance_text

reqt2compJTBL
requirementID (PK)
complianceID (PK)

documentsTBL
documentID (PK)
document_title

proceduresTBL
procedureID (PK)
procedure_text

Relationships:
requirementsTBL.requirementID <-> complianceTBL.requirementID
requirementsTBL.complianceID <-> complianceTBL.complianceID

I'm sure I need to elaborate/clarify, but thanks in advance to any initial help!

[orange]

There are several Document related data models at this site.
Not sure if any apply to your situation directly, but they may give you some ideas.

http://www.databaseanswers.org/data_models/index.htm

You may want to make some test data - samples for each of the proposed tables-
and post that. It would certainly help the rest of us to understand your set up.

You will help yourself greatly if you can identify some of your business rules/facts
see http://www.databaseanswers.org/data_...tion/facts.htm