Question and Answer database

[paulcraigdainty]

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

 

[John_W]

Yes I agree - you need to store all the information in literal form for it to make sense.

Or - more complex - when you change a question, give it a new unique identifier, so in reality it's a different question, even though you may still present it to the marker as Q1, the database knows it's really Q999 or something.

I guess you'd also want to store a record for each different version of your scorecard so you know which scorecard referred to which questions.

 

[paulcraigdainty]

Many thanks for your reply John.

I have attached a copy of the relationships though i can't help thinking it's incorrect.

Also if i am to store all questions and scores in one table how would i define a key?

 

[The_Doc_Man]

The "questionnaire" type of database has cropped up before. Search this forum for "SURVEY" and "QUESTIONNAIRE" for threads. Note that the forum also uses SURVEY-type threads (asking its members to vote), so if the thread you found via SEARCH seems to just be a member survey, skip it as a useful source of information for your problem. (Though it might be a useful source of general information...)