Table design ideas

Other SQL Server Topics
1

Hi,
I need to design a table to store data of a page which contains mostly 50 dropdowns with multiple options in them. Let's say the dropdowns are called Questions.

I'm thinking of creating a table

image
image693×300 6.51 KB

It's possible some questions don't have multiple answers but need free flow text.

To store this free flow text, what is the best design? Add another varchar column to the QuestionCodeValue table?
Thanks

2

As to your q, the actual text response would go in the Result table, right? I would think all you would need in the QCV table is a flag indicating that the answer is freeform_text.

As to your designs, the QCV table's PK first key could should be the Qu_Co, followed, as the second key column, by a unique number, such as a question_sequence or just a fully sequential number if you prefer.

For the Result table, don't you need the Responder in there somewhere? Or do you deliberately want completely anonymous answers? Presumably then you would have a separate table that would tell you who has already answered the qs (?).

3

hi

hope this helps

please let me know

CREATE TABLE Questions (  
    Question_Code INT PRIMARY KEY,  
    Name VARCHAR(255) NOT NULL  
);  

CREATE TABLE QuestionCodeValue (  
    PK_Column INT PRIMARY KEY,  
    Question_Code INT,  
    Description VARCHAR(255),  
    Value INT,  
    Free_Text_Option VARCHAR(255) NULL,  -- Column for free-text responses  
    FOREIGN KEY (Question_Code) REFERENCES Questions(Question_Code)  
);  



CREATE TABLE Responses (  
    Response_ID INT PRIMARY KEY,  
    Question_Code INT,  
    Question_Code_Value INT,  
    Free_Text_Response VARCHAR(255) NULL,  -- Column for free-text responses  
    FOREIGN KEY (Question_Code) REFERENCES Questions(Question_Code),  
    FOREIGN KEY (Question_Code_Value) REFERENCES QuestionCodeValue(PK_Column)  
);
4

Add a new flag column to QCV table to indicate the question has free flow text and leave the description null for this row.

In the UI page, if the question's corresponding QCV row has the flag set then display text field to enter text, store the text in the result table with question code and code value row PK ID.
Sounds good?

5

Appreciate the response. I've a question why to add the Free_Text_Option column to the QuestionCodeValue table as it's already in the Response table?

6

Yes, sounds good. Again, I would absolutely change the key to the QCV table to have the question_code first, NOT just a generic identity first, for best performance. (There is actually not a "rule" that tables should "all" have identity as a meaningless "key".)

2 Likes