[thelist] database eating brain...

Warden, Matt mwarden at odyssey-design.com
Sat Jul 1 12:44:14 CDT 2000

> 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
> I'm trying to do?

Where is the grant table? Do grants only relate to genGoals? If so, you need
a genGoalID in the grant table, and get rid of the grantID from the genGoals
table. However, if a grant can have many genGoal records associated with it,
you need another table:

TABLE: genGoal_Grant_Link
    genGoalID  (PK)
    grantID    (PK)

This creates a m-m relationship between a grant and a genGoal.

Now, what I see misssing is a table that identifies a school. If I'm
understanding it correctly, you'd need some sort of table structure which
held information about a specific school... or even a specific user which
relates to a school. Depending on how you set it up, the above table would
look like this instead:

TABLE: genGoal_Grant_Link
    genGoalID  (PK)
    grantID    (PK)
    schoolID   (PK)


TABLE: genGoal_Grant_Link
    genGoalID  (PK)
    grantID    (PK)
    userID     (PK)

where userID relates to the table appUser (or something other than "user"
which is a reserved word in most databases):

TABLE: appUser
    fname    (pk)
    lname    (pk)
    schoolID (pk)

where the combination of fname, lname, and schoolID *must* be unique. And
schoolID relates to a table called school

TABLE: school
    name           (pk)
    districtID     (pk)

that's how i'd do it. of course, you could go cheap and just make each of
the IDs the pk, but the more I learn more about data modeling, the more I
think that's a waste of a primary key.


More information about the thelist mailing list