03-03-16, 13:40 #1Registered User
- Join Date
- Mar 2016
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.
03-03-16, 13:48 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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
documents (includes reference (FK) to which project)
and an intersection table that associates persons with documentsI'd rather be riding on the Tiger 800 or the Norton