6-challenge, multiplayer & multilanguage Quiz

Hello everyone. I have a few questions for you who have experience with Quiz applications.

Here's what it is. I'm trying to make a multiplayer quiz (for 3 players) who will have 6 groups of questions with different forms ("true-false", "4 answers", "3 replies" & "connect the concepts of" etc). Questions are organized so that there is main group (geography, history, literature, movies, music, celebrities, sports, etc..) Each group has a subgroup (More precise division), and then it has often the second sub-level.

Example: "Movies" are the main group. The subgroups are: "Oskar", "Blocbuster", "European film", "Golden Lion (Venice)", "martial art movies" etc.
Subgroup "Oscar" has its subgroups such as: "Best Picture", "Best Actor", "Best actress" and so on. I hope you understand me.
The point is: The central group has no questions, it is just determinant in the database, the questions are in the subgroup of level 1 or even deeper. Note: All main themes have subgroups of level 1, but not all have subgroup of level 2 or 3

However, this is not the end. Each question has been translated into five languages for players who come from other countries can read and answer in their own language.
Furthermore, each question has a specific name: [I] – for international questions [L] - local questions (this type of question may appear only if all players are from one country). Players will use the application made in Flash.

The questions are :
1. What is the best way to organize a database of questions? As a large or for each language separate database? Give me a suggestion how to organize tables, because I have about 2500 questions, which will be used for 9 different games (but all belong to the group "True / False" or "multiple answers").

2. How do I use the ID issue, because the questions are sorted into groups? Note: It is needed a simple way of marking so database can be updated when it is needed, including import from CSV file. Autoincrement number of [ID] is not an appropriate way.

3. How can I use the RANDOM funkc. to extract from each group only 1 QUESTION? (Example: The first quiz-game has 10 true/false questions. I have 14 main groups. It is needed to choose only one question from first group, subgroups is irrelevant, the main group MUST NOT be repeated)

4. Having in mind that the client is -side Flash applications, it is logical that the communication is done with XML files. So it is a Flash <-> XML <- PHP <- MySQL connection.
Question: This is one way. Is there a better way? Some other variant?

5. There now has the problem. Example: There are many users who are online and use the quiz application. For example, 10,000. of the total, 6000 is pressed Button for "Play game" and appeared in a kind of "lobby" where all participants are sorted into groups of 3 players for a quiz game. That makes about 2000 separate sessions.
Question: What is the best way to generate different XML files, where it can be set up, and how to give it a name?

I have more questions, for start this is most important. Thanks in advance. :)

I am not familiar with XML or Flash, but I am guessing that is not much of an issue for you right now. It sounds like it is going to take some time for you to write this script even if it is just entering the data into your database. I hope you have a good server that can handle the load of so many visitors going to your site, but that's another issue. You only need one databse. When dealing with multiple languages collation may be an issue, but I really doubt it. Most likely you are already familiar with collation or it is not important.

To start you want to create a normalized table. The following can get a little complicated if you are not familiar with normalized tables. I am keeping the following pretty simple for simplicity sake.

First table: contains your questions, answers, quiz format, language, and question ID number.
Second table: tags for each question such as the category used (this can also contain the sub category names or you can create another table for the subcategories). You will also need an ID column and a category ID (or sub category ID as the case may be).
Third table: This table contains an ID column, a tag ID column, and a question ID column.

More tables and relationships can be added, such as a table for the name of the language used and another for the relationship between the name of the language and the table, but that is more complicated than I have done before. In your case it sounds like it would be a good idea.

The query to gather and display the results will look something like this:

Code:

---

SELECT question.ID, question.format, question.answer, question.language, question.text
 FROM question, question_tag_relationships
 WHERE question_tag_relationships.tag_ID=$question_ID
 AND question_tag_relationships.question_ID=question.ID
 ORDER BY RAND() LIMIT 10

---

The category tags are represented by the tag's ID found in the second table. This will allow for tags to be added and renamed easily.

I would practice setting up the tables and the queries before progressing further.

James, I carefully read your post. I visited the link that you suggested, and I understood the basic directions of development database. Following instructions, I'm calculate that I needed 6-9 tables, different table for each type of quiz-game, central table for main category, one table for subdivision and international question, one table for registration of players, his score and time of connection...

Currently, I work on several models database, and soon I will publish a some database structure on this site, so ... expect the comments about them.

Everyone - give a comment, criticize, give advice.

James, thank you for helping me.

I hope that it did help. I remember that learning how to normalize tables was one of the more difficult concepts for me to learn. What you are trying to do sounds like a rather large undertaking, but quite doable and it will draw from many different areas of your coding knowledge. It also sounds like you have a fair amount already.

I will try to help further if I can.