Representing relational tables in ActionScript 3.0

Question

I'm struggling to make a flexible data structure (like a relational database) for a quiz in ActionScript 3.0.

I have a number of questions, each with 2 alternatives:

Questions

| id | alt1    | alt2    | correctAlt |
---------------------------------------
| 0  | Sweden  | Denmark | 1          |
| 1  | Norway  | Finland | 2          |
| 2  | Iceland | Ireland | 1          |

And a number of users with unique IDs:

Users

| id  | age  | sex  | profession | totalCorrect |
-------------------------------------------------
| A5  | 25   | 0    | "Lorem"    | 0            |              
| A6  | 45   | 1    | "Ipsum"    | 0            | 
| A7  | 32   | 1    | "Dolor"    | 0            |

And each user might answer a question:

Answers

| question_id | user_id | answer |
----------------------------------
| 0           | A5      | 1      |
| 1           | A6      | 2      |
| 2           | A7      | 1      |

---

How can I represent this in AS3?
And how can I, when I've collected all the data, answer questions such as:

a. How many users answered question 1?
b. How many % of the users answering question 1 were correct?
c. And how can I sum the number of correct answers for each user and update the totalCorrect column?

Answer 1

I suggest you to store this data in a SQL database.
A SQL database will help you to extract data by your questions.

If you are using Air, this should be easy as Air supports SQLite, a local database.

If you are developing a .swf file, you can connect to a SQL database using AMF or using POST or GET methods to a server who will fetch the data by the SQL request sended using the URLRequest() method.

I strongly recommend to use a SQL database as this is made for your type of questions.

Here's your questions translated to SQL

1. How many users answered question 1?
   ^{You forgot to add id to your answers table}

   SELECT users.id FROM users, answers, questions WHERE questions.id = 0 AND answers.question_id = questions.id AND answers.answer = questions.correctAlt;

2. How many users answered question 1?
   This can be done in two queries. Both will return a number of users. First, all users who answered the question. Secondly, the same as the first question translated.

   SELECT users.id FROM users, answers, questions WHERE questions.id = 0 AND answers.question_id = questions.id;

   SELECT users.id FROM users, answers, questions WHERE questions.id = 0 AND answers.question_id = questions.id AND answers.answer = questions.correctAlt;

3. How can I sum the number of correct answers for each user and update the totalCorrect column?
   You don't even need this sort of column in your table as it can be resolved by a simple query.

   SELECT answers.id FROM users, answers, questions WHERE answers.question_id = questions.id;
   

Answer 2

I took the time to look into your problem in more detail. I created three classes as models for the Question, User & Answer and an AnswerEvent class.

The main class serves as a central point to manipulate data. When a user answers a question , the user's answer(Answer class ) dispatches an event to inform the main class. The answer has a user property , a question property and a isCorrect property, the AnswerEvent contains an Answer object that can be stored in the main class.

After the event has been dispatched , the answer is stored in an array of answers. Since each answer contains the user data as well as the question answered & how it was answered , you can use this array to answer your a, b & c questions.

As for the classes, I followed a similar principle to that exposed in my first reply. I don't think there's enough space here to post all the code so I just posted some excerpts.

//Inside the main class
private var answers:Array = [];

private function init():void
{
   this.addEventListener(AnswerEvent.ANSWER , answerEventListener );

   var q1:Question = new Question( 0 , ["Sweden" , "Denmark"] , 0 );
   var q2:Question = new Question( 1 , ["Norway" , "Finland"] , 1 );
   var q3:Question = new Question( 2 , ["Iceland" , "Ireland"] , 0 );

   var user1:User = new User( 5 , 25 , 0 , "Lorem"  );
   var user2:User = new User( 6 , 45 , 1 , "Ipsum"  );
   var user3:User = new User( 7 , 32 , 1 , "Dolor"  );

   //if the answer is correct , the totalCorrect property is incremented
   // in the User class, check the Answer class below for an explanation of the 
   //parameters     
   user1.answer( new Answer( this , user1 , q1 , 1 ));

}

private function answerEventListener(event:AnswerEvent):void
{
   answers.push(event.answer);
   trace( this , event.answer.isCorrect );
   trace( this , event.answer.user.age );
}

Here's a model for the Answer class, I didn't add the getters for lack of space. The AnswerEvent extends the Event class and add an answer property of type Answer

public class Answer
{
   private var _question:Question;
   private var _answer:int;
   private var _user:User;
   private var _isCorrect:Boolean;

   public function Answer(dispatcher:EventDispatcher , user:User , question:Question , answer:int)
   {
    this._user = user;
    this._question = question;
    this._answer = answer;

    // not essential but will help iterate thru correct answers
    // the _answer property should be _answerIndex really, in order not to be confused 
    // with an Answer object ( quick job ! )        
    if( question.correctAnswer == answer )
     _isCorrect = true;

    // the this keyword corresponds to this answer          
    dispatcher.dispatchEvent( new AnswerEvent(AnswerEvent.ANSWER , this) );
   }
}