INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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.

RE: Possible Many-Many-Many? Or something better...

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

RE: Possible Many-Many-Many? Or something better...

Quote (OP)

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.

RE: Possible Many-Many-Many? Or something better...

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close