Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2016
    Posts
    1

    Question Unanswered: Best Way to Structure Table Relationships (New Database)

    Entirely possible I'm over-thinking this, but here goes. Been a while since I've built anything in Access (~10 years). Currently putting together a project tracking system for our team, using Access 2007 (though may eventually migrate this to SQL for better mutli-user support). All my prior work has been in Access 2000 and 2003.

    Trying to keep track of the status of an overall project, and each project could have 0-10 individual documents associated with it. I have no plans to store the documents themselves in the database; I just need to keep track of what is going on with each document individually. I'd like to be able to run reports that indicate the total number of projects associated with a person, and the total number of documents associated with a person.

    So Person A has Project 1 with 0 docs, Project 2 with 3 docs, and Project 3 with 1 doc.
    Person B has Project 1 with 2 docs, Project 2 with 2 docs, Project 3 with 3 docs.

    Reporting would need to include each person's Project total and Document total. Other reports would detail out a complete list of what someone is working on with all associated other information, as well as reports on document type by person, open/close date differentials, etc.

    Would I be better off building this as a Project table, then a Document table that references the project name? Or should I be breaking this into more tables (maybe one for each document type) to address long term scale issues? Or is there some better way for me to build this? Tables for other elements (associated procurement owner, business owner, vendor, etc.) have been easy to tie in, but this structure/linkage seems like the most important one for long term stability of the database.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    persons
    projects
    and an intersection table that identifies documents associated with a person and a project

    ...or if more than one person my be associated with documents associated with a project
    persons
    projects
    documents (includes reference (FK) to which project)
    and an intersection table that associates persons with documents
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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