[thelist] database eating brain...

[McCreath_David]

This is an awfully long post for the end of a work week and beginning of a
holiday weekend...sorry.

I'm building an application for one of our departments that's quite beyond
the scope of anything I've done before and the database is killing me. Using
NT/ASP/SQLServer. 

We have this program called Safe and Drug Free Schools that gets a big
federal grant every year. Then our schools apply to the District SDFS office
for "mini-grants" (weird mental image of Hugh Grant and Dr. Evil...). This
online application is going to take the place of the bewildering and
poorly-laid-out paper application. Okay, so my problem is this:

The school does a "Needs Assessment", using one of seven pre-designed tools
(surveys) or a tool of their own design. In the application I have a form
where the applicant chooses the tool used in the needs assessment. For the
greatest flexibility, I have a table called "naTools" that will create the
original list. When the applicant selects a tool, it adds a row to a table
called "naToolsUsed" that will be used later in the application. After
entering a tool, they can enter another one, or go on to the "Goals"
section. On the Goals page, they enter a general goal and a measurable
objective for that goal. (They can have more than one goal, just like they
can have more than one tool. Each goal adds a row to the "genGoals" table
with the grantID from the "grant" table.)

Here's where I start getting myself in knots:

For measurable objective, they can select one OR MORE tools from their Needs
Assessment to measure the outcome of this objective; that's why I have
"naToolsUsed" -- to build that list of tools from their needs assessment.
Now am I right in thinking that each goal is going to need a separate table
of tools used as well? Or is there a way to add them to the "genGoal"
table?"

Here's what I have sketched so far. (Pardon my psuedo database markup here,
please.)

TABLE "naTools"      <-- This is used to build the initial menu.
 naToolID (PK)           It's in a table so that SDFS can edit it.
 naToolName              It is not related to a specific grant.
 naToolDesc          <-- In case they use their own survey.

TABLE "naToolsUsed"  <-- This is built from the input of the form that
 grantID (FK)            uses "naTools" in a drop down, and is only
 naToolID (FK)           used to build a checkbox list specific to this
 naToolUsedID (PK)       grant.
 
TABLE "genGoal"      <-- This is specific to each grant, and each grant
 grantID (FK)            can have more than one goal.
 genGoalID (PK)
 genGoal
 objective

TABLE "genGoalTool"  <-- This is used to record which tools from 
 genGoalID (FK)          "naToolsUsed" apply to this specific goal
 grantID (FK)
 naToolUsedID (FK)

Am I overcomplicating this? Is there enough information here to explain what
I'm trying to do?

------------------------------------------
David McCreath   Web Development
(907) 742-4647   Anchorage School District
                 http://www.asd.k12.ak.us/