December 15th, 2012, 07:36 AM
Designing my first database
HI, I am trying to design a database to store multiple choice questions, which will be generated by the user. The user will decide on how many questions and how many choices for each question
This is the first time I have tried to design my own database and seem to struggling with working out how to do it.
The rows I have set up are:
value (for the correct or incorrect answer)
The idea is the user generates the quiz, and the questions get put into the database accordingly.
My problem is understanding how to better set up the questions and choices - my thinking is -, assuming that a user sets up four choices per question there will be three blank rows apart from the choices column, and the remaining three choices would not be linked to that question.
How do I set up database up better so that I can link all choices with the relevant question?
December 15th, 2012, 07:51 AM
Well Id say a better database structure is to design two separate tables for quiz and questions. Your quiz and questions relationship can be as simple as one to many(a quiz has many questions, but each question belongs to only one quiz), or as complex as many to many(a quiz can have many questions, and a question may belong to many quiz).
Now assume you have an one to many relationship. The quiz table stores the quiz's id, title and questions. The questions can be joined by serializing an arrayobject, or use the PHP string to array converter functions(explode/implode). Anyway, you can retrieve an array of questions for each quiz.
For the questions table, it stores an id of its own and holds a reference to the id of the quiz it belongs to(or the title if its a unique property). A question can have many choices, so the field choice needs to store a serialized arrayobject or imploded array. The value/answer, on the other hand, has an one to one relationship with the question, it should be rather straight-forward to design.
If your quiz and questions have many to many relationship, each question will have to hold an array of ids that reference to the corresponding quiz. I am not sure if this is what you will be doing, but many to many relationship is difficult to handle. Should you choose this path, please be ultra careful.
I suppose I should stop here since all you were asking was how to design a better database. Of course you need more work than just structuring the database, Id say google the concept called ORM. It helps a lot in situations like yours.
Comments on this post
December 15th, 2012, 08:30 AM
It sounds to me like a many-to-many relationship will be required. I can't imagine questions not being repeated on different quizzes. That being said do not shy away from that concept as it is not really that difficult. You create a 3rd "join" table that contains quiz ids and question ids. There will be numerous quiz ids in one column and each will have an associated question id in the other. The collection of quiz ids from that table provides all the question ids for that particular quiz.
P.S. This is probably the wrong forum for this question at this stage of your development. While PHP and databases go hand-in-hand, database design questions are better off in that forum.
Last edited by gw1500se; December 15th, 2012 at 08:32 AM.
There are 10 kinds of people in the world. Those that understand binary and those that don't.
December 15th, 2012, 08:40 AM
please do not store serialized arrays or comma separated lists or something like that in your database. That's pretty much the worst you can do with regard to data integrity, efficiency and usability. You might wanna read up on normalization to avoid typical errors.
But like gw1500se already said, this is a database question, so it should be in the database forum.
December 15th, 2012, 02:22 PM
Well I suggested these since Vbulletin uses serialized arrays/objects for storing multiple PM recipient and style list. I learned quite a bit of stuff by looking at its source code lol. Thought it should be a good practice since VB does that, but guess even VB does not guarantee quality design.
I was somewhat aware of the concept in database normalization though, just never really heard of that term. Thanks for bringing it up, will take a look.
December 15th, 2012, 05:04 PM
Even when you don't specifically know the concept of normalization, there are obvious problems with storing data in some list format:
- You'd have to declare the column as a generic VARCHAR, and a VARCHAR can contain anything. There's no way to enforce valid data, let alone correct references.
- You can no longer process the data with the database system itself. Instead, you basically have to re-implement core SQL functionalities like JOINs in your application. Not only is this unclean and error-prone, but it's also a major performance killer.
So whenever you find yourself trying to store an array-like structure in a database, stop right there. A one-to-many relationship is implemented by putting a reference column into the "many" table that points to the "one" table. A many-to-many relationship is implemented by creating a third table as gw1500se already explained.
Getting back to the original question:
OK, there are three aspects:
- a quiz, which belongs to a certain user and consists of multiple questions
- a question, which belongs to a certain quiz (I wouldn't necessarily allow "reuse" of questions in multiple quizzes)
- a choice, which belongs to a a certain questions and is either correct or wrong
So the basic table layout would look something like this: ("PK" stands for "primary key", "FK" means "foreign key")
- user_id PK
- quiz_id PK
- user_id FK, references users (user_id)
- question_id PK
- quiz_id FK, references quizzes (quiz_id)
- choice_id PK
- question_id FK, references questions (question_id)
Comments on this post
December 16th, 2012, 06:57 PM
Jacques1 thanks for that description of how its done, from the other posts people had kind enough to post, I was going down that line, im glad to know I was heading in the right direction after all this time
Before i carry on with this task though I will take a more in depth look at normalization, and joining tables as I have on skimmed that chapter so far.
Thanks to everyone for your help