Access Table Design - Document Tracking

I'm having a problem with the design and relationships of my tables in a new database I'm creating.

It's a document release tracking database created in Access. Each document has to go through a review cycle and a sign-off cycle before it can be "released". Each document has a program (and possibly a sub-program as well) and a doc type associated with it which determines who is required to sign off on it during both the review cycle (1st) and the sign-off cycle (2). (Those who review are also required to sign-off so I'm dealing with two different cycles but only one set of authorization signatures).

So, I have document info, program info, sub-program info, doc type info, and authorized signature info. I also need a user table which houses all user names, loginIDs and email addresses because the db is required to automatically email individuals when a document that needs their signature is placed on the review/sign-off table.

Because each document can go through the cycle twice, I create a cycle indicator column (which reads R or S) and linked it to the DocID to create a multiple-field primary key.

I'm still having a problem figuring out which table should contain what and how to link them together to make sure they work properly.

Currently, my tables look like this:

Main Doc Info
---------------
DocID (part of primary key)
DocTitle
Program
SubProgram
DocType
EMType
OrgDate
Designer
Checker
ReviewSO (part of primary key)
DirSO

Program Info
-------------
ProgName
ProgDesc
ProgConNo
PMngr

Sub-Program Info
-----------------
SubProgName
SubProgDesc
SPConNo
SubIPT

Document Type Info
-------------------
DocType

Authorized Signatures
----------------------
AuthProg
AuthFunct
AuthSig
AltSig
AuthMngr

Users Info
-----------------------
UserName
UserEmail
UserLoginID


Can someone please point me in the right direction? I have worked with databases which were created by someone else and I have created smaller, less complicated databases that work great but this is the first, somewhat complex one I've had to create from ground up. Any help would be appreciated.

From what I understand, this is a good start.


Main Doc Info
---------------
DocID (part of primary key)
DocTitle
ProgramID (foreign key to program info table)
DocTypeCode
EMType
OrgDate
Designer
Checker
ReviewSO (part of primary key)
DirSO

Program Info
-------------
ProgramID
ProgName
ProgDesc
ProgConNo
PMngr

Sub-Program Info
-----------------
SubProgramID
ProgramID (forein key to program info)
SubProgName
SubProgDesc
SPConNo
SubIPT

Document Type Info
-------------------
DocTypeCode
DocTypeDescription
ReviewAuthSigID (authorized to sign for review)
SignoffAuthSignID (authorized to sign for sign off)

Authorized Signatures
----------------------
AuthSigID (primary key)
AuthProg
AuthFunct
AuthSig
AltSig
AuthMngr
UserLoginID (foreign key to user info)

Users Info
-----------------------
UserName
UserEmail
UserLoginID

Shailesh,

Thank you for your help. Couple of questions. :)

1. Is there a need to link the DocTypeCode in the Main table to the DocTypeCode in the Document Type Info table?

2. Is there a need to link the AuthProg (in Authorized Signatures Table) to the ProgramID (in the Program Info table)?

3. If the same individuals have to "touch" the same type of documents for both the review and sign-off cycles, is it necessary to have both the ReviewAuthSigID and the SignoffAuthSigID in the Document Type Info table? (i.e. John Doe signs off on all installation drawings for Program "A", first in the review cycle and then in the sign-off cycle. In the review cycle he notes his changes and in the sign-off cycle he signs that he approves it).

Secondly, regardless of the answer to the above question (#3) is there a need to link the AuthSigIDs (in the Document Type Info table) to the AuthSigID (in the Authorized Signatures table)??

In the above example, the Document Type Info table is not linked at all and the Authorized Signatures table is linked only to the user table. How will these be used by the Main table without being linked?

Thanks for your help.

1. No if you are not maintaining a separate table for doc type.

2. If you eliminate doc type table then you could link program to signatures table, but you will need two columns one each for each cycle.

3. Yes you need two columns, you will enter same value in both, if you don't do it you can't enter two names for regular cases.

ReviewAuthSigID (authorized to sign for review)
SignoffAuthSignID (authorized to sign for sign off)

link doc type to signature table


This is just one way to do it, you could have many different solutions, which one is best depends on your data.

I am BRAND NEW at this whole access programming thing. I too am trying to design a db that will track documents for my awards and decorations/special projects office at my squadron. I am in the USAF. We have a document accountability problem that i am trying to resolve. Any helpful advice in getting started with this project? Should I sit down first and diagram what I want this db to do?