Results 1 to 5 of 5

Thread: Access Table Design - Document Tracking

  1. #1
    Join Date
    May 2003
    Posts
    25

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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

  3. #3
    Join Date
    May 2003
    Posts
    25
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  5. #5
    Join Date
    Jun 2008
    Posts
    1
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •