Test / Exam / Answer Scheme with Grade

Question

I am writing an application that will generate checks for our locations. Basically, think of them as a form of medical examination. Each “inspection” will contain a series of questions and answers. Answers can be either numerical (1,2,3,4,5 - which will represent their point values) or multiple choice ("Yes", "No"), which will have a map for points (1 for "yes", 0 for no) and flat text answers that will not be matched with points, but can be used at the application level for averaging. So, for example, we could have a field for “sauce temperature”, which has no points, but can be used to communicate along the road.

Questions can be reused in several forms of verification, but can have different point values. So you can answer.

I find it difficult to understand the circuit for this. My instinct says that EAV will be a good way, but the more I think about it, the more I think about a better data warehouse model.

In particular, I had a problem in determining the best way to match min_points, max_points and no_points with each question / answer. That's where I think I will have to use EAV. Actually, I'm actually stuck. If it was a survey or something where there were no points or the same value for each answer, that would be pretty simple. Question table, answer table, some template tables for input type, etc. But since each question MAY have a point value, and this point value may vary depending on which location this question uses, I am not sure how to proceed.

So the following questions:

• Was the food hot [Yes, No] Possible points = 5 (5 for yes, 0 for no)
• Was the food delicious [1,2,3,4,5] Possible points = 5 (1 for 1, 2 for 2, etc.)
• Duty manager [Yes, No] Possible points = 5 (5 for yes, 0 for no)
• Was the building clean [1,2,3,4,5] Possible points = 10 (2 for 1, 4 for 2, 6 for 3, etc.).
• Was there professional staff [Yes, No] Possible points = 5 (5 for yes, 0 for no)
• Freezer [numeric text input]
• Duty manager [text entry]

Since all answers can have different data types and point values, I'm not sure how to build a database for them.

I think (The rest of the tables, names and other details are inactive or changed for brevity)

CREATE TABLE IF NOT EXISTS inspection( id mediumint(8) unsigned not null auto_increment PRIMARY KEY, store_id mediumint(8) unsigned not null, inspection_id mediumint(8) unsigned not null, date_created datetime, date_modified timestamp, INDEX IDX_STORE(store_id), INDEX IDX_inspection(inspection_id), FOREIGN KEY (store_id) REFERENCES store (store_id)ON DELETE CASCADE, FOREIGN KEY (inspection_id) REFERENCES inspection (inspection_id)ON DELETE CASCADE) CREATE TABLE IF NOT EXISTS input_type( input_type_id tinyint(4) unsigned not null auto_increment PRIMARY KEY, input_type_name varchar(255), date_created datetime, date_modified timestamp) CREATE TABLE IF NOT EXISTS inspection_question( question_id mediumint(8) unsigned not null auto_increment PRIMARY KEY, question text, input_type_id mediumint(8), date_created datetime, date_modified timestamp) CREATE TABLE IF NOT EXISTS inspection_option( option_id, value) 

But here where I am stuck. I'm not sure how to build answers to questions to take into account points, points, and different types of data.

In addition, I know that I will need mapping tables for stores for checks, etc., but I left them all now, since this is not important for the question.

So, should I make a table for answers where all possible answers (created from the options table or entered as text) are stored in this table, and then a mapping table for matching the "answer" to the "question" (for any specific inspection) and save are there items?

I just don't think right. I could help.

Answer 1

There is no right or wrong answer here, I just throw some ideas and discussion points.

I would suggest that the basic “unit” should not be a question, but a couple of questions + an answer like (for example, 1-5, text, or something else). It seems to me that Was the food hot / range 1 to 5 and Was the food hot / text description so different that you go crazy trying to connect the question with two (or more) types of answers (not to mention the answers to keys for of these answers - do not pay attention that at the moment I am taking it back later). Name the pair the QnA element. You can make a lot of similar pairs, but hey, this is what you need to work with.

So, you have a "pool" of QnA elements. How are they selected for use? Are specific forms (or questionnaires) constructed from elements in the pool, or are they randomly selected each time the questionnaire is filled out? Are the forms specifically related to the location, or can be used anywhere? How fussy are they when creating their forms / questionnaires? How the QnA elements are assembled / linked together and / or there the final results are very important, and you should fully understand before you start writing code if you really don't like to rewrite the code.

Given a specific QnA element, you should also have a “response key” for that element — the means by which a given response is measured (based on the type of response to the element): zero, value, value * 2, whatever. This, apparently, can vary from use to use (questionnaire to questionnaire: is it different from the location where the questionnaire is presented? If so, how and why?) Are there standardized response keyword algorithms (in addition, always the value Value * 2, etc.), or are they also extremely free? Determining how they are used / related to QnA elements will be necessary for proper modeling.