Question Best design forward

[SueBK]

My Access mentor has moved on and I'm learning that database design is always best if bounced around between minds. Particularly, when you're not actually a programmer!
My main data consists of - a list of projects, a list of yes/no questions that are to be answered for each project, a set of rules for each question. The questions are grouped by an environmental element (eg, 5 relate to air issues; 2 to water issues, etc) I have two (main) tables -
* tblIssues - the list of questions and a variety of fields that state what the rules are if the answer is yes. For example - question 1, if answered yes, might have a 'red' risk level, might need a particular permit. Question 2, might only be an 'orange' risk level, may not need any permits, but might need a particular action to be noted as required in the contract.
* tblProjects - the list of projects (and their various details)
I then created a third table (tblPrjIssues) that has a record for every question for every project (eg if there were 10 questions and 10 projects I'd have 100 records) (I have closer to 40 questions and potentially an infinite number of projects).
I'm beginning to think that this is not the best way to go about it. My issues are:
every time a project is added, I need a mechanism to create the question records in tblPrjIssues.
I can't work out how to design a form that is filtered on the project, and then groups the questions by their element (ie there is a heading "Water", and then all the water questions, before moving to a new 'page' with the heading "Air".)
I did design a form which used "if 'yes', dlookup the rules'; looks great - as soon as yes is ticked the fields all turn the risk level, the permits, surveys, searches, contract conditions, all come through beautifully. I was going to use the same premise to design a report. But then the issue of the fundamental table design occured to me, and I'm not so sure.
I'm using Access 2007, if that makes any difference.

 

[jzwp22]

First, I would have a category field in the issues table to distinguish in which group the issue/question falls. That category field would be a foreign key to a table that holds all possible categories


tblIssues
-pkIssueID primary key, autonumber
-txtIssue
-fkCatID foreign key to tblCategory

tblCategory (will have a record for each of the following: air issues, water issues, etc.)
-pkCatID primary key, autonumber
-txtCategory


I was concerned by the following:

...and a variety of fields that state what the rules are if the answer is yes.

If an issue/question has many rules associated with it, that describes a one-to-many relationship, so the rules should be in a related table not as fields in the tblIssues.

tblIssueRules
-pkIssueRuleID primary key, autonumber
-fkIssueID foreign key to tblIssues
-txtRule

Now, it seems that the rules themselves not the issues are what is important to the project in that they determine the what has to be done. Am I correct?
If so then why not join the applicable rules to the project rather than the issues?

 

[SueBK]

Perhaps examples of my data would have helped keep it clear.

An issue would be "Is the site within 200m of a RAMSAR wetland?" All issues are written so that "yes" is the risk. If the answer is 'no', then no further action is required. If the answer is 'yes', a risk category is assigned, possible permit applications are triggered, searches required. So, for the given question - the risk level is high, a site survey is not required, no further searches are required, a permit will be required, but no other actions are required. These five items are my five additional fields in the issues table. The rules are very much assigned to an issue, rather than a project.

I managed to contact my old mentor (somewhere in northern Vietnam). His advice, which is working well -
- keep the issue questions and the rules in one table; this allows rules to be updated (it may be that at some point we're granted an exception to a particular permit, for example)
- keep the projects in a separate table
- the third table combining issues and projects (so each question can be answered for each project) can be updated using a query with the first tables (and no joins) - ie showing every project with every question, and then filtered to only show "null" values against the third table.

As soon as I work out how to delete records from the third table if an issue or project is deleted - I'll be home and hosed.

I do also have an 'elements' table, which assigns a particular environmental element to each issue.

I appreciate your feedback. Just getting my thoughts 'out there' helps the working through process.

 

[jzwp22]

Your mentor has given you some good advice.

As soon as I work out how to delete records from the third table if an issue or project is deleted - I'll be home and hosed.

I would not delete records, just mark them as solved/inactive/complete etc.; one field should do it. I usually use a date field so that I know when it occurred as well.