Separate subjective score categories from rubric sheets - virtual category

[jpschewe]

There are some cases coming up where we'll need this.

Tasks:

• [x] add to XML
• [x] Create table in database for each virtual category to contain the average score for each goal
• [x] Add virtual category to example database dump
• [x] Add step to standardization to populate the virtual category tables
• [x] add the virtual categories to the final_scores table
• [x] Add the virtual categories to the final computed scores report
• [x] add to award summary
• [x] add to editor
• [x] TournamentAdvancement
• [x] ScaledSubjectiveByAwardGroup
• [x] Make XML check that virtual category names are unique along with subjective score categories

[jpschewe]

If a score sheet goal can be used in multiple categories, I can have a category made up of goals referenced from score sheets. However when I want to populate the score sheet with the values from the database how do I map that back?

The easy solution is to just store the scores in the categories like I currently do and then throw an exception if the scores don't match up when creating the populated rubric sheet. Although that also comes up when populating the subjective judging application.

Another idea is to store scores separately in the database outside of categories and score sheets. Then put it all together in code as needed. However that will require rewriting the code that does normalization as well as the code that loads and saves scores.

[jpschewe]

In the end adding a virtual subjective category seems like it's going to solve the issue.

[jpschewe]

Instead of a table per virtual category, I could go with a table for all virtual categories with columns:

• tournament_id
• team_number
• virtual_category_name
• goal_name
• goal_score

The the query for final scores is

SELECT team_number, sum(goal_score) as computed_total
  FROM virtual_category 
    WHERE tournament_id = ? 
      AND virtual_category_name = ? 
  GROUP BY team_number