[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
what
> 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)
-or-
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
userID
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
schoolID
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.
--
mattwarden
mattwarden.com
More information about the thelist
mailing list