I’ve been working on a project recently that involves a desktop AIR application running (mostly) offline with a local SQLite database. Occasionally the app will synch collected data remotely with a central server that will have an administrative and reporting web front end. Various parts of the AIR application involve filling out a series of multi-choice questions. Considering that the data will be used by two different systems, and anticipating that questions and answers may want to be updated from the admin portal, it seems a good choice to make things as dynamic as possible based on data from the db. It’s not a difficult problem to solve, and I’ve done it a time or two before, but since it’s fresh in my head and the code is in front of me, I’m going to use a couple blog posts to document the strategy. The SQL presented is pseudo code and will not actually execute.
Part 1 is getting the database structure in place.
The first two tables are lookup tables used to catorgorize our questions and answers.
questionTypes (
pid integer pk,
name text
)
answerTypes (
pid integer pk,
name text
)
QuestionTypes may hold names of groups of questions, like ‘Screen1′, ‘Screen2′.
AnswerTypes will hold names of groups of answers. Some entries in my tables are ‘Difficulty’, ’Frequency, ‘Yes/No’.
Now we can define our main Questions and Answers tables.
questions (
pid integer pk,
questionTypeId integer fk,
answerTypeId integer fk,
sequence int,
text text,
active int
)
answers (
pid integer pk,
answerTypeId integer fk,
sequence int,
text text,
active int
)
Records in the multiChoiceQuestions table hold a relation to a questionType and a answerType. Every question presented together should get the same questionType and the answerType will indicate which groups of answers should be associated with this question. Text is the question text itself and sequence is the order.
Records in the multiChoiceAnswers table hold a reference to answerType. So for the answerType ‘Difficulty’, text for records in this table may be ‘Easy’, ‘Sorta Easy’, ‘Sorta Hard’, ‘Hard’, ‘Impossible’.
At this point we have an easy way to do CRUD on questions, answers, question grouping and answer grouping by just making db updates.
The active flag becomes important when we start to look at the response tables.
responses (
pid integer pk,
sessionId int fk,
questionId int fk,
)
responseAnswers (
pid integer pk,
responseId int fk,
answerId int fk
)
We have our main response table which holds a reference to a questionId and sessionId. SessionId would have references to the user / time/ whatever, and we also have a join table between responses and answers. This facilitates having more than one answer for a question.
We can now associate an answer or set of answers with a specific question for a single user at a particular point in time. Our main goal is accomplished from a schema standpoint.
Back to the active flag for a moment. If a question or answer gets updated or removed, we don’t want to directly update the current row or else previous responses will hold a reference to a question or answer that was different in the past when the user actually answered it. A new record will be created with a new id, and the old question/answer will be set to inactive. This insures that when reporting on results, we will see what the user actually saw at the time they answered.
The next part will show code examples of how i keep this data structured in memory, Flex components used to dynamically display the question and events to keep everything in synch.
