Project Proposal Structure Questions

[mmchaley]

Hello all -

I am in the process of building a DB for the purpose of developing proposals based on past project and personnel experience.

The major elements of the DB are Past Project Info, Company Resume Info, Staff Resume Info, Proposal Info.

I am trying to determine the best structure to capture who worked on what project for both Company info and Staff info. I am not sure if I should set up an intermediate table which tracks the primary key of the project and the primary key of the Company and Staff where my fields in the table are the roles required on the project i.e. my intermediate table fields would be ProjectID, ArchitectCoID, ArchPMID, ArchDoRID, SuperintendentID, ProjectManagerID, SafetyOfficerID...

Or concatenate these IDs into 2 separate fields on the Projects table?

Or is there a better solution?

The reason I am asking is I can't see the advantages or pitfalls to either solution and not sure how it will play out when I start extracting the data for the proposal development piece.

My other question is the need in access 2010 to separate the database into the tables which are heavy text and the tables which link project photos and document links.

The DB will be housed on a server and used on a in-office desktop. Each project will have 3 to 5 proposal quality photos and there will be a couple of hundred projects in the database.

Thanks - I look forward to your input

Mark

 

[spikepl]

You have told os specifics of an application exiting in your mind and ask for judgements on that. We have no idea of your context so you need to :

1. Backup and tell us the story - what is this all about and for whom? No DB gobbledygook but just a narrative.
2. Define your intended objects sufficiently also for outsiders to grasp - we have no clue what function/content of, say, "Past Project Info" is

For starters, read about naming convention - blanks and weird characters in object/field names will kill you in the long run: http://www.access-programmers.co.uk/forums/showthread.php?t=225837

 

[mmchaley]

Spikepl - Thanks for the reply

Back story - we are a General Contracting firm who often needs to put together technical proposals illustrating our expertise and past experience. To illustrate this expertise we profile construction projects we have completed along with the people who worked on those projects. These people also include designers and engineers from other firms.

The way the information is tracked and collected currently is on the fly, in random places on the server and as needed (Translate into not well, not accessible, and not complete).

As this is a slow time for bidding projects, I decided to build a database to capture, track and be able to filter these data.

The goal of the database is to store the information from past construction projects which will be used in future technical proposals.

These data include management staff, design team, key subcontractors, client contact info, schedule and price data, narratives describing the work, issues which cropped up and how we solved them, photos and other construction industry standard building metrics.

The Proposal side of the database will require the user to input some parameters such as project dollar range, project type, scope, etc. These questions will filter the few 100 projects we will have in the database to the most relevant to the proposal requirements. It will also allow the user to review past proposals which were similar in nature.

Once the past project resumes, people resumes, and other required information is selected out of the database it will be merged into pre-built word templates.

We typically have 3 weeks to write a technical proposal and they often come in waves so we are working on 2, 3 or more proposals at one time. I want to decrease the time it take to actually gather, order and format the majority of the data so we can spend more time editing the base information to that specific proposal.

Hope this helps paint the picture. Let me know where I need to provide more clarity.

Cheers,
Mark

 

[mmchaley]

For starters, read about naming convention - blanks and weird characters in object/field names will kill you in the long run:

Thanks - I read the post you linked, I work to follow it. I am wonder if there is something specific which I wrote that prompted you to include this? I re-read my post after reading the thread and can't see what I missed. :banghead:

Thanks,
Mark

 

[clarkcovey]

Hello Mark,

Let me start with my disclaimer: I am no expert.

Usually, when I start a project, I find it helpful to draw out a bubble chart with the work flow and see if any data is being used in multiple areas and so to avoid redundancy, I may separate that into separate tables.

You mentioned various people to track: "management staff, design team, key subcontractors, client contact info" so if these are used in multiple areas, you may wish to have a table for personnel. Can they be consolidated into the same table or is there quite a bit of variance to what you collect on each?

Keep in mind if you have a table with numerous cells that are not being used, that is a lot of wasted storage and memory space. If you create your personnel table and let's say the contractors' licenses must be tracked, would you need to track the same for non-contractors?

Good luck with your project.

Clark

 

[The_Doc_Man]

When at the front side of a project, I always try to do this:

1. Identify the common elements that could possibly go into a particular single table. So... "Personnel" - probably single table. "Completed Projects" - probably single table. "Architectural Company" - probably single table. etc.

2. Now decide how they fit together. This is where relationships become paramount - and understanding how to make the relationships work is even more important. You described something that we call a "junction" table - a table that lists a project and a person. You use these when you have many/many relationships. I.e. many people worked on many projects, with high degree of overlap in all cases. It is not necessary for this junction table to be "pure." I.e. you can have role information if you wish... like in one entry, John Smith was an assistant project engineer but in another entry, he is the lead project engineer. That would be part of the junction table, not the project or person table.

3. Here is where the "Old programmer's rules" come into play.

Rule 1: Access can't tell you anything you didn't tell it first. Practical meaning: Decide ahead of time what you want to get out of Access because you are going to have to include in your design a way to get that INTO Access.

Rule 2: If you can't implement the design on paper, you can't implement it in Access. Practical meaning: Before you build the first table, draw up a full-blown relationship diagram. I recommend a white-board, some markers, and some sticky-notes. Make a sticky note for each class of entity you will track - persons, projects, etc. Then draw lines between the notes to show how they relate to each other. Perhaps make special notes associated with the lines to show how a relationship would be exercised or where it becomes important. I.e. if you have a report that depends on a relationship, there had better be a line there for the relationship - and maybe a note describing the report.

4. There is no need to store pictures in your database. You can store LINKS to those pictures easily enough. On forms/reports where those pictures need to be used, have an image control and leave it with no associated image. Then, in some form or report code, have a little bit of VBA to associate the image with the picture file when you open the implied document. Keep your pictures in a folder or a tree of folders (makes no difference to me). Then in the table where you need a picture reference, just store the text of the file specification, which only needs to include device, path, and name (if you always use the same kind of picture) or add the file type (if you have .GIF and .JPG and .WMF mixed image sources.) Load up the reference and then repaint.

5. Resumes COULD be handled the same way as pictures since they can vary quite a bit with regard to content. However, there you would want to have a large text box instead of an image control. Note also that you can declare a text box to be RTF rather than pure text. So if your resumes were all stored in .RTF files, you could just read them into the appropriately formatted box.

 

[mmchaley]

Thanks all for the replies. Just got back into this after the break.

I solved my original problem, but realized I caused myself another problem.

I set my field names in a table to the roles of the individual so I could track who did what on specific projects. This works well until someone decides to add a roll which does not exist in my table.

I think I might just Change my Project Team table to have the ProjectID, OrgIDs, ClientID, PersonnelIDs and ClientContactIDs - then Concatenate the IDs to specific fields