Structuring Tables Sharing Field Values

[dashingirish]

I'm having trouble figuring out how to best structure the tables for the following scenario and I'm hoping you'll have some brilliant ideas.

The db is for taking assessments. Each assessment consists of numerous items for which the user must assign a level of importance (i.e., Very important, Not at all important, etc) and a status (i.e., Stable, In crisis, etc.). The problem I'm having is that although the status choices remain the same, the definition of each status choice changes for every item in the assessment. Thus, a status of "In Crisis" might be "Does not obtain enough food for family" for one item and "Language development is well below age norms" for another. The user needs to see the definitions for each item's status in order to determine the status level. Complicating things further is each status choice (stable, in crisis, etc) must have a numeric value associated with it (i.e., stable = 5, in crisis = 1).

My question is, what's the best way to structure the tables for this data collection? I've considered a number of scenarios, but they all seem too cumbersome. I may be overthinking this by now, but I cannot find a simple and elegant solution.

 

[jzwp22]

Since each assessment item can have many status choices that describes a one-to-many assessment. Also, a status choice may apply to many assessment items, so another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (assessment items and status choices in your case), you have a many-to-many relationship which requires a junction table.

So a table for the assessment items

tblAssessmentItems
-pkAssessItemID primary key, autonumber
-txtAssessmentItemDescription

And a table for the status choices

tblStatusChoices
-pkStatusChoiceID primary key, autonumber
-txtStatusChoice
-longChoiceRank

And the junction table

tblAssessmentItemChoices
-pkAssessItemChoiceID primary key, autonumber
-fkAssessItemID foreign key to tblAssessmentItems
-fkStatusChoiceID foreign key to tblStatusChoices

Now in tblStatusChoices I included a rank field. This would be used for the numerical value you mentioned. Now if the ranking is different for a status choice depending on which assessment item to which it is associated (in the junction table) then I would move the rank field to the junction table.

 

[dashingirish]

That is just perfect. Thank you for your quick and very thorough response.

 

[jzwp22]

You're welcome. Good luck with your project.