Schema database for quiz answer

I am working on a personality test that will contain 112 questions.

Instead of creating 112 columns, like this:

 Schema::create('grupos', function (Blueprint $table) {
            $table->increments('id')->unsigned();
            $table->integer('q1')->default(0);
            $table->integer('q2')->default(0);
            $table->integer('q3')->default(0);
            $table->integer('q4')->default(0);
            //....
            $table->integer('q112')->default(0);	
        });

I thought of creating a single column $table->text('answer'); and store a java script object, like so:

answer: {
    1: 50,
    2: 47,
    3: 12,
    ....
    112: 33,
}

This way my database is cleaner. For I store the answers to the questions in a single column.

But as it will be a field that will be updated whenever a new test is answered, I would like to know your opinion if this is a good practice or if I could have a problem in the future.

What do you think ?

piljac1

5 years ago

Level 28

Firstly, you can't store a JS object in the database, but you can store JSON.

Secondly, I wouldn't store JSON in that case (and in most cases), because it is terrible to query. I would use a structure along those lines:

users

id
name
email

quizzes

id
user_id

questions

id
title

answers

id
question_id
title

answer_quiz

id (this is optional, but with eloquent I always like to have an ID on all tables)
answer_id
quiz_id

I don't know what your business rules are (ex. Do you have to be logged in to take the quiz ?), but I think the base structure I laid out above would be a good start, because it is modular and normalized. If you have a new question to add, you don't have to create a migration to add a new column.

1 like

mvnobrega

5 years ago

Level 3

That's good logic, yes. But I want to avoid using Joins as much as possible. As it is a multi-language site, it can have many users responding and quickly have a very large database.

I'm almost doing it the way you suggested. But using a single column to store all the questions and another column to store all the answers in JSON format, it seems very nice

Is it really a bad idea to store JSON in a single table?

piljac1

5 years ago

Level 28

Why don't you want to use joins ?
What a multi-lingual site has anything to do with either approach ?
Even if you have more entries in your database, any relational database engine is way more optimized for querying columns than searching info in a JSON string
I suggest you Google about JSON usage in relational databases and make your own idea about it. I personally think it's bad, but you might end up thinking otherwise.

1 like

martinbean

5 years ago

Level 80

@mvnobrega Model your entities properly. Don’t show-horn stuff in JSON columns.

Relational databases like MySQL are meant to store relational data. That’s literally their purpose. They’re better at storing and querying large numbers of records that going through JSON blobs.

2 likes

mvnobrega

5 years ago

Level 3

Right!

I'll do it the conventional way then.

Thanks for the suggestions

Please or to participate in this conversation.