Project management database design help

[Sketchin]

Hello,

I am in the planning stages of creating a sort of project/programs management database and would like to verify my table structure. Our company is a sort of investment firm that invests in companies projects, gives grants for trade show attendance, gives grants for commercialization services..etc. Currently we track all of our project information in an excel spreadsheet that we are quickly out growing. We need to migrate all of this data into a database to make it easier to work with and remove the margin for error that Excel inherintly has.

I have created a main projects table with a bunch of lookup tables for things like project program, geomatics discipline, resource sector, contacts, company name...etc.

My question is whether or not its ok to use the same projects table when I dont need all of the same fields for every type of project. For example, our Industry investment program would use all of the fields in tblProjects where our CSS program would only use a few of the fields in tblProjects.

I have attached a jpg of my relationship structure for clarity, as I have done a terrible job of explaining what I am doing..lol

 

[MarlaC]

The short version is, it's fine and your design looks reasonable.

The longer version is that it depends to an extent on what proportion of projects would be Industry vs. CSS, as you may get moderately better resource allocation by breaking out the lesser-used fields to a linked table if a significant proportion of projects are CSS. You don't have an unreasonable number of fields and they don't appear to be multimedia types (which occupy more memory), so it's doubtful you'd get much benefit in this case from splitting out to another table.

 

[jdraw]

Design seems reasonable given the amount of info provided. Does the project management have to tie in to other systems in your company--such as Finance and Personnel?

Have you considered commercial packages?
What do you expect out of the application? Gantt charts, work breakdown, critical path???

 

[Sketchin]

Thanks Marla, that is really the reply I was looking for. When I was thinking about the forms I need to make, I suddenly had a bad feeling that I should be using all fields for all programs. Now that I know its ok to have unused fields for specific programs, I won't worry about it!

 

[Sketchin]

Design seems reasonable given the amount of info provided. Does the project management have to tie in to other systems in your company--such as Finance and Personnel?

Have you considered commercial packages?
What do you expect out of the application? Gantt charts, work breakdown, critical path???

If it was up to me, I would suggest the company purchases a pre-made system. As it stands right now, I am in the requirement gathering stage, to find out if the design is feasable for us.

As a sidenote, I am not a database designer, so that will factor in to my decision to take on this project or not.

 

[jdraw]

The key, in my view, is to properly scope the task before you.

In your requirements make sure you identify dependencies --
eg.
must tie in to SAS Financial System, or XYZ Personnel system.

Need to interact via hand held devices/smart phones...

We will need graphs and charts.....detailed financials

We need ability to make changes easily.........

You might want to look at the features of a couple of Project Management Applications and compare your needs with their features.

Seems to me you're designing the logical database; then must do all the other pieces and coding... outputs, reports, GUI, interactions....
then compare with existing packages....

Just googled and found this
http://try.wrike.com/free-project-management-software/?gclid=CJ2Q5fjEjbwCFahj7Aodgh8A2g#

and this
http://en.wikipedia.org/wiki/Comparison_of_project_management_software

 

[Sketchin]

Thanks for the input. The real problem we are having with finding a suitable system is that its part project management and part investment tracking. We invest in companies, and then track their project progress in a broad sense (quarterly reports). The thing is, we don't control their timelines in the traditional project management sense, so we don't need a specific project management system. Also, the system does not need to tie into any other systems.

 

[jdraw]

As I said the key is to scope the task before you.

Seems you have Project management (and I have no idea of the types of projects you deal with or how much detail is involved) and
Investment in companies and monitoring their projects.

Can you mock up some scenarios based on your company records over some timeframe.

You should be able to identify

Projects
People
Milestones
Locations
Finances

Investments
People
Financial Info
Status Reports

What don't you get now that is needed etc?

Write some scenarios along this sort of style

Narrative --from RogersAccessLibrary

ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

There must be something needed - reports, timeliness... - that isn't currently available from current approach.