Question And Answer Database

Hi

I am trying to create a question and answer database that will hold agent scores for future reference.

My idea is that I will have the following tables:

•Team
•Marker
•Agent
•Scorecard
•Question & Score

I want to have a Scorecard form which the marker completes based on the agents performance. The key requirement of this database ability to change the questions periodically and if necessary amend the score applicable to each question. This is why I think it’s best to have a Question and Score table so the questions can be updated and the changes will be reflected on any new scorecards that are created.

I have created the database structure which I think is correct and normalised to the best of my ability. The scorecard table grabs questions and applicable scores from the Question & Score table the marker completes the answer field based on the agent’s performance. So, on each Scorecard that is created the current questions and scores are displayed.

So here’s my question: Should the Scorecard table hold questions and scores, or should it just hold answers? I am thinking questions and scores. I know this is against the rules of normalisation as this data is already held in the Question & Score table however what if I change my questions then want to look at a historical scorecard (completed when the question were different). The questions and scores will have updated to the current set making that scorecard inaccurate.

Thanks