0

I have 3 tables questions, answers, and user_answers. All questions have a related answer set in the answer table and users answers are stored in the user_answer table along with the question_id that the answer is for.

Imagine a checkbox type survey with one question with a radio button.

I'm wondering if it is possible to constrain the user_answers table to only allow 1 answer per user_id for that specific question_id but allow multiple answers for all other questions.

I've looked into solutions but can't quite find anything to match my specific use case.

Any help is appreciated, Thanks!

djfried
  • 27
  • 1
  • 11
  • How about a check constraint - https://stackoverflow.com/questions/3880698/can-a-check-constraint-relate-to-another-table – Evgeni Dimitrov Oct 16 '17 at 21:05
  • I don't understand your wording. What do you mean by "allow 1 answer per user id for **that specific** question but allow multiple answers for all other questions"? Which **specific** question? Do you mean, allow only one answer for every combination `(user_id, question_id)`? If so, that is your answer: create a (tuple-level, not column-level - so it has to be an out-of-line constraint, at the table, not the column level) UNIQUE constraint on `(user_id, question_id)`. For a given, fixed `user_id`, this doesn't mean the answer to all questions must be the same. It means one answer per question. –  Oct 16 '17 at 21:36
  • Looking at the data model - why is it necessary to have an `answers` table? Each question has a unique correct answer, which would be best stored in the `questions` table. You only need two tables total, not three. –  Oct 16 '17 at 21:38
  • You might want to have a look at ["function based index as conditional unique key"](https://dba.stackexchange.com/questions/7443/function-based-index-as-a-conditional-unique-key). – halfbit Oct 16 '17 at 21:48
  • Don't add such constraints on table, it will have unnecessary bottlenecks.Handle it in your application code - PL/SQL / JDBC with exceptions and error messages. – Kaushik Nayak Oct 17 '17 at 04:50
  • Its like a form where all the questions are select all that apply except for one which is a radio button meaning only 1 option can be selected. I was taught that the best place to enforce data integrity is at the database layer so I though that there might be a solution to this problem there. For the time being I am handling it in my service code because that was the easiest to implment – djfried Oct 17 '17 at 17:14

1 Answers1

0

You have three tables:

  • QUESTIONS (QID, QTYPE, ...)
  • ANSWERS (AID, QID, ...)
  • USER_ANSWERS (UAID, QID, AID, UID (user ID), ...)

In case of possible multi-answers to a question, there will be more than one answer ("check all that applies" type of question) in ANSWERS, and user can check many, resulting many references to same (QID, UID) but with different AID in USER_ANSWERS. In case of one selection allowed, there will be more than one answer in ANSWERS, but user can only select one, resulting a single reference to (QID, UID) in USER_ANSWERS.

My suggestion would be the following - to force at the database level

  1. Introduce a new column in ANSWERS: AQ_REF (Answer Question Reference). This is populated with QID when the type of question allows only single answer, and with AID when the type of question allows more than an answer
  2. Use this in addition to AID in USER_ANSWERS

So now, you have the table structure like this:

  • QUESTIONS (QID, QTYPE, ...) -- PRIMARY KEY (QID)
  • ANSWERS (AID, QID, AQ_REF, ...) -- PRIMARY KEY(AID), FOREIGN KEY(QID), UNIQUE(AID, AQ_REF), CHECK AQ_REF IN (AID, QID)
  • USER_ANSWERS (UAID, AID, AQ_REF, UID, ...) -- PRIMARY KEY(UAID), FOREIGN KEY(AID, AQ_REF) REFERENCES ANSWERS(AID, AQ_REF), UNIQUE(AQ_REF, UID)

Example Data:

QUESTIONS:

QID, QTYPE

Q1, 'S' -- for single answer

Q2, 'M' -- for multi answers

ANSWERS:

AID, QID, AQ_REF

A1, Q1, Q1 -- note this has AQ_REF = Q1 as for all Q1 answers

A2, Q1, Q1

A3, Q1, Q1

A4, Q2, A4 -- note this has AQ_REF = A4

A5, Q2, A5 -- note this has AQ_REF = A5

A6, Q2, A6 -- note this has AQ_REF = A6

USER_ANSWERS:

UAID, AID, AQ_REF, UID

UA1, A1, Q1, U1

UA2, A4, A4, U1

UA3, A6, A6, U1

BA.
  • 924
  • 7
  • 10
  • Thanks for the creative solution! I'm going to try to implement this but for the time being I am just handling this case in my application code. – djfried Oct 17 '17 at 17:15