help with design of db to track documents

[April]

I'm using Access 2002 at work and have set up a database
to control the documents within our company. The idea is
to assign a 4 digit number to the document and a revision
number. I initially set it up with a composite key of doc
number and revision number, but now am rethinking that as
I try to determine a way to link the documents to other
tables which show 1. various manuals containing these
documents (so reports can be run when revisions are made
and manuals are kept up to date) and 2. departments who
utilize the documents (many are cleaning or quality
checklists). My problem with the composite key is that I
always want to be linked to the most current document and
cannot think how to do this without a lot of extra steps.
I feel I'm missing an easier way to do this. Any
suggestions? Thanks in advance! April (pelican@no
spam.pair.com)

 

[Tim Ferguson]

My problem with the composite key is that I
always want to be linked to the most current document and
cannot think how to do this without a lot of extra steps.
I feel I'm missing an easier way to do this.

My impression is that you are missing an entity. What this sentence means
is that the manuals are related to the Document, not to a particular
Revision -- these are separate entities. I would go for

Documents(*DocNumber, Author, CommissionedBy, etc...)

Revisions(
DocNumber,
RevNumber,
RevisedBy,
DateSubmitted,
etc...

Primary Key (DocNumber, RevNumber)
)

Manuals(
ManualCode Primary Key,
RefersTo Foreign Key References Documents,
etc.
)

and so on. It's easy enough to get the MAX(Revisions.DateSubmitted) to get
the most recent revision of a document that a particular manual is linked
to.

Hope that helps


Tim F