How to change this many to one relationship to one to one?
.net, c#, sql
Solution
Another approach:
- Drop the `QuestionID` from your `Answers` table.
- Drop the `AnswerID` column from your `Questions` table.
- Create a new table, `QuestionAnswer` (or something more meaninful, like `Exam`, `Test`, etc.), which has a `QuestionID`, an `AnswerID`, and an `IsCorrect` flag.
- Map all `Answers` to their `Questions`, and mark which one is correct.
Now you can re-use answers for other questions as well, and every question and ever answer only exists once. It may also be useful if you want to add additional metadata about the question answer combinations. For example:
- A `Sequence` column to control the order in which the answers appear.
- A `PointValue` column to help produce a final score or grade.
Problem
I have two tables as in the snapshot below. ![Diagram][1] Scenario: A question should have only one correct answer, but can have many ( 3 in my case) wrong answers ( like a quiz show). Problem: `Questions` table has multiple answers in the `Answers` table, but only one correct answer. The correct answer is the `AnswerID` in `Questions` table and it is related to `AnswerID` column in `Answer` table. But it shows up as a Many to One relationship ( please see the bolded fields). I have applied UNIQUE constraint to AnswerID in Question table but still it shows a many to one relationship. What can I do so that each AnswerID column entry is linked to a single AnswerID in the Question table? or is this ok as it is? Thank you QUESTIONS TABLE: ``` CREATE TABLE [dbo].[Questions]( [QuestionID] [int] NOT NULL, [QuestionText] [nvarchar](max) NOT NULL, [AnswerID] [int] UNIQUE NOT NULL, [ImageLocation] [ntext] NULL, CONSTRAINT [PK_Questions_1] PRIMARY KEY CLUSTERED ``` ANSWERS TABLE: ``` CREATE TABLE [dbo].[Answers]( [AnswerID] [int] NOT NULL, [AnswerText] [nchar](50) NOT NULL, [QuestionID] [int] NOT NULL, CONSTRAINT [PK_Answers] PRIMARY KEY CLUSTERED ```