How to create a table for a row and link between them?

Question

0.00/5 (No votes)

See more:

i have a table in which store name,question of users and i want store multiple answers from multiple user for a question.

username
``````````
name question
-------------------
alice why use java?
thomes what is difference between c and c++?

i want like this

name question answer
-----------------------------------------------------
alice why use java? answer1,answer2,answer3

What I have tried:

i want to store make a page like this site where for a question have multiple answers.

Posted 17-Sep-16 21:21pm

Member 12677198

Updated 17-Sep-16 21:43pm

v2

Add a Solution

2 solutions

Solution 2

In your example you state that you would like to store multiple answers from a person to a single row. That's not advisable. Instead you should use an associative table.

So the design shold be something like

user
----
- userid
- name

question
--------
- questionid
- questiontext

answer
------
- userid
- questionid
- answer

For more information, have a look at Associative entity - Wikipedia, the free encyclopedia[^]

Posted 17-Sep-16 21:43pm

Wendelius

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-09-17T21:39:00

Solution 1

Simple: you need multiple tables.
The first table stores users:

ID          int, identity
UserName    NVARCHAR

The second stores questions:

ID          int, identity
Question    NVARCHAR
AIDCorrect  int, foreign key to Answers.ID
AIDError1   int, foreign key to Answers.ID
AIDError1   int, foreign key to Answers.ID

Then answers:

ID          int, identity
Answer      NAVRCHAR

Then the linking tables:

ID          int, identity
QID         int, foreign key to Questions.ID
UID         int, foreign key to Users.ID
GivenAnswer int, foreign key to Answers.ID, Nullable

You can then use SQL JOIN to retrieve the info you need.

Posted 17-Sep-16 21:39pm

OriginalGriff

Comments

Karthik_Mahalingam 18-Sep-16 4:51am

5

Member 12677198 18-Sep-16 4:52am

thanks,
but why we create AIDCorrect,AIDError1 and AIDError1 column in question table?

OriginalGriff 18-Sep-16 5:06am

Because you want to be able to associate the correct answer and a couple of incorrect answers with the question. You can't just grab three answers at random, because A) the "right" answer is unlikely to be there, and B) the "wrong answers will probably be so obvious no-body would use them:
Question: What is 2 + 2?
A1: Because it's Object oriented.
A2: 3.1415927
A3: SELECT TOP 10 UserName FROM Users ORDER BY DateOfBirth DESC

See what I mean? :laugh:

Member 12677198 19-Sep-16 4:44am

thankss,

OriginalGriff 19-Sep-16 4:52am

You're welcome!