If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design of Risk Database
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, |
#2
|
|||
|
|||
Design of Risk Database
hi,
DontKnow wrote: 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 -- |
#3
|
|||
|
|||
Design of Risk Database
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" wrote: hi, DontKnow wrote: 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 -- |
#4
|
|||
|
|||
Design of Risk Database
hi,
DontKnow wrote: 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!! A Manage Table: PMID - PK ProjectID - PK date - Date Projects have can have zero, one or more managers? 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 -- |
#5
|
|||
|
|||
Design of Risk Database
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" wrote: hi, DontKnow wrote: 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!! A Manage Table: PMID - PK ProjectID - PK date - Date Projects have can have zero, one or more managers? 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 -- |
#6
|
|||
|
|||
Design of Risk Database
hi,
DontKnow wrote: 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 -- |
#7
|
|||
|
|||
Design of Risk Database
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 "DontKnow" wrote: 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, |
Thread Tools | |
Display Modes | |
|
|