Design of Risk Database

[DontKnow]

Hi Guys,

I am tring to design a risk database. The schema that I currently have is
like this:

A ProjectManager table:
PMID - PK
userid (this is used for determining who is logged into the database)
surname
firstname

A Projects table:
ProjectID -PK
ProjectName

A Manage Table:
PMID - PK
ProjectID - PK
date - Date

A Risks table
RiskID -PK
Nameofrisk
mainproblem

A Mitagation Table:
ProjectID PK
RiskID - PK
date -PK
probabilityoffailure
consequence
strategytorepair
newconsequence
newprobability

The requirments are that:
Each Manager is capable of manageing several projects with read and write
access
All other managers not owning a project only have read access.
Each project is capable of having many risks

There is to be a form to be able to select each managers projects ie. a
manger will have a form that has a subform whereby the admin guy can load the
Project Managers with all different projects.

On selection of a project the user is required to see all the different
risks associated with that project.

a seperate form is required for all relavent risks for a project.

Do you think that my schema is capable of producing these outputs. Any
suggestions greatly appreciated. I think it is easier to fix things at the
start(requirments) than waiting until I have built it and then realise the
error of my ways!!

Again any suggestions would be of extreme help!

Cheers,

 

[Stefan Hoffmann]

hi,

A Manage Table:
PMID - PK
ProjectID - PK
date - Date

Projects have can have zero, one or more managers?

The requirments are that:
Each Manager is capable of manageing several projects
Each project is capable of having many risks

Separate your concerns into data managment and application design...


mfG
--> stefan <--

 

[DontKnow]

Normally each project may have one project manager but if he leaves then the
project is allocated to another Project manager.

Usually a project has one manager but a project manager may have one or more
projects runnig at a time!!

I hope this helps!!

Cheers,

 

[Stefan Hoffmann]

hi,

Normally each project may have one project manager but if he leaves then the
project is allocated to another Project manager.

Don't use terms as normally or usually. You need to be precise here, to
get your design correct.

Usually a project has one manager but a project manager may have one or more
projects runnig at a time!!

So the question still needs to be answered.

If a project must have exactly one manager then you normally would store
the manager in the project table:

A Projects table:
ManagerID
ProjectID -PK
ProjectName

When a project must have one or zero managers then the design would
depend on two facts in relation to the amount of data:

a) Which is case with the higher probability?
b) How often do you need to query this information?

a) If the probabilty is high for the case that you have no manager, then
you should consider storing the managers in a separate table. Otherwise
you would create systematically Null-values in the manager column of
your project table. This is an unwritten consequence of the rules of
normalization

http://en.wikipedia.org/wiki/Database_normalization

0st NF:
Store Data, not NULL. Avoid the systematically creation of NULL-values.

(you may credit me

b) When you need often the information about the manager assignment to a
project, then you may ignore a) because it would cost too much time
querying it.


mfG
--> stefan <--

 

[DontKnow]

Thanks for your input Steffan,

The project is to be maintained by one person. I am told that on occasion
if the first person who was assigned to the project leaves then someone else
is required to take on the prject.

the database is to be able to have a form that recognises the user as a
project manager and automatically displays all the projects that have been
assigned to that project manager. On selction of the particular Project (via
double click) in the subform a new form that displays the all of the
associated risks for that project would be displayed.

Does this sound feasibl;e from the design that I have provided??

Cheers again for your help!!

 

[Stefan Hoffmann]

hi,

The project is to be maintained by one person. I am told that on occasion
if the first person who was assigned to the project leaves then someone else
is required to take on the prject.

Then I would store the manager in the project table and do it without
your additional table.

[..] Does this sound feasibl;e from the design that I have provided??

Yes, it does.


mfG
--> stefan <--

 

[roccogrand]

DontKnow,

I'm working on my third project management information system (PMIS) and
feel a little qualified to comment on your application.

My approach on each has been to use the Project_Number as the primary key.
Using a subform, each project could have one or more project managers.
This model also allows me to have numerous modules (phases of a project) for
each project in one of my PMIS's. I now have eighty modules in this PMIS,
some with subforms. For example, each project has multiple project
members--using multi-value fields.

In my Task Database, I use Access 2007 multi-value fields for the task
members. These are great for users and was easy for me.

Three of the modules in my PMIS are for risk management. Many of the fields
are memo fields. The single-term fields, such as Priority (High, Medium,
Low), use look-up tables.

Your question about limiting certain users to specific forms and reports is
one of Access security. I am trying to understand security myself and don't
have a best practice for you, especially if you are using .accdb as your file
format. My understanding now is that you can only password protect your
database with .accdb. But there is more that I need to understand about
using Access 2007 with SharePoint and Groove in this regard.

HTH

David