0

another database structuring question, hope you guys dont mind :D

i am having this video quiz on my web, with this kind of flow:

  1. the viewer will watch a video
  2. that particular video has a question attached to it
  3. and it also has 2 questions, one of them is false and of course the other is true

so far i have figured out two database design for this

the 1st one is:

table: video 
fields: id, filename, type, size, created

table: question 
fields: id, question, right_answer, wrong_answer, video_id

the 2nd one, i am separating the question and the answer on each different tables

table: video 
fields: id, filename, type, size, created

table: question 
fields: id, video_id, question

table: answer
fields: id, answer, video_id, status

the status field in answer's table is to indicate whether the answer is right or wrong, probably using tinyint by the value of 0 and 1

which one would you guys recommend me as a better approach and why, and since i don't really understand database normalization, is there any easy to understand article so that i can improve my knowledge regarding that, any help would be much appreciated, thanks

Regards

Update:

thank you everybody for pointing out a better approach for my database design, since everyone is suggesting that i should use boolean or bit as field type and i always use tinyint for fields like this before :p, so i search over for a solution on what type should i use in mysql to represent boolean, and i stumble upon this Which MySQL Datatype to use for storing boolean values from/to PHP? and according to the answer of that question i should use tinyint(1), so i guess it's safe to say that i will keep using tinyint for now :p

thanks again everyone

Community
  • 1
  • 1
littlechad
  • 1,202
  • 17
  • 48

3 Answers3

2

Forgiving the ASCII, I recommend the following:

VIDEO                Question             Answer
|- id       1--.     |- id       1--.     |- id
|- filename     '--* |- video_id     '--* |- question_id
|- type              '- question          |- answer
|- size                                   '- correct (bit, not tinyint)
'- Created

That gives you the freedom of adding multiple questions on a video, and multiple answers to each question. Also, i suggest using a bit (not a tinyint) to flag the "Correct" answer. I also tried to show how the keys would align in this scenario.

Brad Christie
  • 100,477
  • 16
  • 156
  • 200
  • if i am thinking about the flexibility for further development of the module, this does make sense, thanks Brad – littlechad Nov 23 '10 at 15:48
1

Intuitively, I'd go for the first solution, as you will most likely need to retrieve both questions at the same time anyway.

By the way, if you go for the second option, then I would advise you to use a boolean value for the "status" field, instead of a tinyint.

0

If you plan to add more answers, then take the second way, otherwise use the first one.

AndreKR
  • 32,613
  • 18
  • 106
  • 168