Storing Multiple Choice Values in Database

Storing multiple choice values in database

You might not be missing anything now, but when you're requirements change you might regret that decision. You should store it normalized like your first instinct suggested. That's the correct approach.

What you're suggesting is a classic premature optimization. You don't know yet whether that join will be a bottleneck, and so you don't know whether you're actually buying any performance improvement. Wait until you can profile the thing, and then you'll know whether that piece needs to be optimized.

If it does, I would consider a materialized view, or some other approach that pre-computes the answer using the normalized data to a cache that is not considered the book of record.

More generally, there are a lot of possible optimizations that could be done, if necessary, without compromising your design in the way you suggest.

The optimal way to store multiple-selection survey answers in a database

This really depends on a lot of things.

Generally, storing lists of comma separated values in a database is bad, especially if you plan to do anything remotely intelligent with that data. Especially if you want to do any kind of advanced reporting on the answers.
The best relational way to store this is to also define the answers in a second table and then link them to the users response to a question in a third table (with multiple entries per user-question, or possibly user-survey-question if the user could take multiple surveys with the same question on it.

This can get slightly complex as a a possible scenario as a simple example:

Example tables:

Users (Username, UserID)
Questions (qID, QuestionsText)
Answers (AnswerText [in this case example could be reusable, but this does cause an extra layer of complexity as well], aID)
Question_Answers ([Available answers for this question, multiple entries per question] qaID, qID, aID),
UserQuestionAnswers (qaID, uID)

Note: Meant as an example, not a recommendation

Any good method to store Multiple Selection Value in database?

It's probably a lot easier to store these using the bit data type in SQL Server. MSSQL will compress multiple bit columns into bytes containing up to 8 bits. For example, if you have 9 bit columns, it will take up 2 bytes total.

But if you have a really large number of bits, then it will just make for poor database design to break them out into separate columns in which case perhaps you can use a fixed-length binary data type which you can access as a byte array in .NET or VB.

How can I get the multiple choice values into a SQL-Table row

Given the database setup you choose, you could do:

if (isset($_POST['email'])) {

   $sonne      = ($_POST['email'] == 'Ja') ? '1' : '0';
   $erdbeere   = isset($_POST['erd'])      ? '1' : '0';
   $himbeere   = isset($_POST['him'])      ? '1' : '0';
   $schokolade = isset($_POST['schok'])    ? '1' : '0';
   $vanille    = isset($_POST['van'])      ? '1' : '0';

   $query = "INSERT INTO auswahl (sonne,
                                  erdbeere,  
                                  himbeere,  
                                  schokolade,
                                  vanille) VALUES ($sonne,     
                                                   $erdbeere,  
                                                   $himbeere,  
                                                   $schokolade,
                                                   $vanille)";

    mysqli_query($link, $query);   
}

I combine all the answers into one insert query.

The ... ? ... : ... thing is called a Ternary Operator.

NOTES:

You can only insert these PHP variables into the query directly because you know they will always be either zero or one. In all other cases use prepared statements, to prevent SQL-injection.
Why do you abbreviate things to erd and him? Does that help readability?
Your database design is very poor. Suppose your have 20 questions, how many columns will you need in your database? This was not part of the question, and I will therefore not show an alternative, but you should really reconsider this design.

How can I store multiple values in SQL?

Use a third table borrowed books which would link books and users

How can i store multiple values on a single mysql column?

This is a very simplistic view but gives an idea of a typical structure for this.

users

id	name
1	User 1
2	User 2

Storing a multiple choice quiz in a database - deciding the schema

You're second schema is the better one, because it models the actual domain: each question has a set of answers. Even if you can "compress" the data by storing duplicate answers once, it does not match the actual domain.

Down the road you'll want to edit answers. With schema 1, that means first searching if that answer already exists. If it does exist, you then would have to check if any questions still rely on the old answer. If it did not exist, you would still have to check if any other questions relied on that answer, and then either edit that answer in place or create a new answer.

Schema 1 just makes life really hard.

To answer your index questions, you would need to add an index on questionId. Once you have that index, looking up answers for a question should scale.

Now, on a completely different note, why use a database for this? Consider storing them as simple documents in a standard format like json. Anytime you query a question, you will almost always want the answers, and vice versa. Instead of executing multiple queries, you can load the entire document in one step.

If you then find you need more advanced storage (queries, redundancy, etc) you can move to a document database like MongoDB or CouchDB.

Storing Multiple Choice Values in Database