klkl

I’ve done quite a bit of work on the project (see the progress page) but I wasn’t sure what to document first because the application is going to store all the information in a MySQL database I thought I’d talk about the structure of the base (or most important) tables.

Questions

The application is going to revolve around questions so there obviously needs to be a table to store some of the details of each question. Each question will need an ID it will have some question text, it will also have a correct answer.

The answers are stored in another table so the question table will hold an identifier that relates to the correct answer instead of the correct answer itself. This is because the same answer may be used more than once (TRUE / FALSE for example) and referencing the answers instead of replicating them will save space.

Some question types are complicated so there will also be instructions to go with the question so the person knows what is expected of them. Again these will be repeated several times and so stored in another table.

So the Question table would look something like;

• Q_Questions
  • ID: int(4)
  • QText: text
  • CorrectAnswer: int(4)
  • Instructions: int(4)

Answers

The answers table is much simpler seen as it only needs to store the actual answer and its ID.

• Q_Answers
  • ID: int(4)
  • AText: text

Lookup Table

A question may have more than one answer and an answer may be suggested for more than one question. This is modeled using a look up table is used. The look up table is a table that records which answers appear with which questions. This is achieved with two one-to-many relationships. The look up table has two fields…

• Q_QAL
  • QID: int(4)
  • AID: int(4)

…one that refers to the ID of the question and another that refers to the ID of the answer. Both fields are assigned as the primary key, this ensures that there are no duplicate entries. Whenever you want to associate an answer with a particular question you create a record in this table with the respective IDs. This way you can have as many or few answers associated with a question.

Instructions

Each question will only have one set of instructions so there is no need for a look up table but the instructions should be stored separately from the questions as mentioned previously so the table would look something like this;

• Q_Instructions
  • ID: int(4)
  • IText: text

Relationships

I haven’t mentioned relationships in depth in this post or the other tables of the database because I’m trying to keep each post simple and focused on one topic. I’ll go into more detail about the other tables of the database such as those dealing with multiple users and user specific tests in another post.