Possible Many-Many-Many? Or something better... 1

[DBLoser]

I'm building a database for document distribution. Right now I have a many to many relationship between Document and Revision.

Document
DocumentID (PK)
DocumentTitle

DocumentRevision
DocumentID (FK)
RevisionID (FK)

Revision
RevisionID (PK)
Revision

I also need to associate each DocumentRevision with one or more projects AND it is valid to have two different revisions of the same document on different projects or on the same project (for example, we are finishing one project, received a new revision for another quantity...). So I thought I'd just throw in another many-to-many by adding ProjectID to the DocumentRevision table with the same RevisionProject to Project relationship as before. But there is no FK in the DocumentRevision table to join to the PK of ProjectID in the Project table (access complains). How can I implement this many-many-many? Also, I do need to track both ways, documents down to projects and projects back to documents.

Thanks for any ideas.

 

[johnherman]

Not sure why Access won't let you do this. I belive it is doable in other DB's, like SQL Server and Oracle, which I mostly work with. One possibility, and not sure how this would work with Access or the Access front-end. You could create a triangle. Project table being separate with two more bridge tables for many to many with Document and with Revision.

====================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[MajP]

DocumentRevision table to join to the PK of ProjectID in the Project table (access complains)

What do you mean access complains? I have never seen Access complain when you try to add a field to a table. You should definitely be able to have three foreign keys in your revision table.

ProjectID, DocumentID, and RevisionID

But unless I do not understand your buisiness model, a revision refers to a document, I am not sure that a revision is a stand alone entity.

So I would think the revision table has a FK to a document

Revision
revisionID (PK)
documentID (FK)
fields unique to a revision (Title, date, number etc...)

then you simply need
tblJunction_Projects_Revisions
ProjectID(FK)
revisionID(FK)

Since a revision is already associated with a document through the revision table no need for document ID in the junction table.

 

[DBLoser]

MajP,

I get what you are saying. I shouldn't treat the revision as a many-to-many; revisions are not stand alone entities.

Thanks!