Adding Array of Strings as Column SQL

Question

CREATE TABLE [dbo].[Questions] (    
    [QuestionID] INT           NOT NULL,
    [Question]   NCHAR (300)   NOT NULL,
    [Choices]    NCHAR [200]   NOT NULL, -- something like this
    [Answer]     NCHAR (200)   NOT NULL, -- one of the indexes in Choices
);

I have multiple choice questions that I am storing into a database, and I would like to keep all information grouped together in one table. The QuestionID and Question is not difficult, but I don't know how to set up some array of strings to store the possible answers (A: one, B: two, C: three, D: four) into the Choices column and to have the Answer column store a copy of the correct choice or the index of the correct choice (whichever is easier).

Many responses I've read quickly suggest that a text file would be easier, however, that is not something I want to do.

This is a personal project (not to be used in any real production), so it doesn't have to be secure or safe by any means. Any hack-y solutions that work are perfect.

It very depends on the database you're using. I'm afraid it's not possible to help you without such information. Unless you would use 1:N relation, which would be the best solution and possible in all relational databases. But you wrote you don't like it. — David Ferenczy Rogožan
– David Ferenczy Rogožan, Commented Mar 18, 2016 at 17:13
A 1:N relation refers to using a text file, then? If it really is the only choice, I will use it. The reason I said I wouldn't like it is all of the text processing I have to do in my C# code. The database queries are much easier to write and I don't have to worry about malformed data — user3465668
– user3465668, Commented Mar 18, 2016 at 17:14
What's the text file in a relation with the database? I definitely didn't mean anything like that. 1:N relation means 2 tables, where there is one column in the first table containing relation to the primary key of another table. — David Ferenczy Rogožan
– David Ferenczy Rogožan, Commented Mar 18, 2016 at 17:18
But as I wrote you didn't say, what database you're using, which is the most important information, since arrays are implemented in various databases differently. Anyway it's not a good solution for many reasons and you should avoid it in a favor of 1:N relation always when possible. — David Ferenczy Rogožan
– David Ferenczy Rogožan, Commented Mar 18, 2016 at 17:20

Rob K · Accepted Answer · 2016-03-18 17:29:15Z

2

You should use a separate Answers table, something like this:

CREATE TABLE [dbo].[Answers] (
    [AnswerID]   INT NOT NULL PRIMARY KEY,
    [QuestionID] INT NOT NULL, -- linked to Questions.QuestionID
    [Choice]     NCHAR [200]
);

Then you make the answer field in the Questions table link to the AnswerID of the actual answer.

(N.B. this is not a text file, this is a second linked table)

ETA

You would change your question table to this

CREATE TABLE [dbo].[Questions] (    
    [QuestionID] INT           NOT NULL,
    [Question]   NCHAR (300)   NOT NULL,
    [AnswerID]   INT        -- this is linked to Answers.AnswerID
);

Questions.AnswerID is the index of the row in Answers which has the correct answer for the Question.

edited Mar 18, 2016 at 17:29

answered Mar 18, 2016 at 17:16

Rob K

8,9662 gold badges35 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user3465668 Over a year ago

So how will the choices be stores? as their own separate column? Columns choiceA, choiceB, choiceC, choiceD?

David Ferenczy Rogožan Over a year ago

Definitely not! You would need to add a column always when you need to add a choice. That's very bad design. Each choice should be a row.

David Ferenczy Rogožan Over a year ago

This is exactly what I suggested in my comments. It's a proper normalized design for such use case.

Rob K Over a year ago

@user3465668 I expanded my answer to show how your Questions table should be structured.

Ross Presser Over a year ago

I would like to add that if these questions really are multiple choice, the AnswerID probably should be CHAR(1) and not INT. The primary key for the Answers table would then be a composite one: (QuestionID, AnswerID). The reason for these changes is that it makes the AnswerID closer to the actual data -- you can more easily see that a user chose choice C. However many people hate composite primary keys.

|

Collectives™ on Stack Overflow

Adding Array of Strings as Column SQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related