[thelist] database eating brain...

McCreath_David McCreath_David at xmail.asd.k12.ak.us
Fri Jun 30 18:20:33 CDT 2000

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

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"

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

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)

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

More information about the thelist mailing list