2

I'm not necessary sure if the "wizard" is the correct term for it, but essentially what I have is a form that have a specific set of questions that a user must answer in order to determine which option is the best solution for them.

I have the following tables:

  • Question
  • Answer
  • QuestionAnswer
  • WizardOption

What I'm having an issue with is creating the relationship between QuestionAnswer and WizardOption. (I'm not sure if I'm just over thinking it; I'm new to designing a database structure).

QuestionAnswer Table

  • QuestionID INT
  • AnswerID INT

WizardOption Table

  • WizardOptionID INT
  • WizardOptionText VARCHAR(25)

To connect the two, is it ideal to create another table, like so:

WizardOptionByAnswers Table

  • WizardOptionID INT
  • Answers VARCHAR(100)

And then in the Answers column I would store the correct order of answers needed to match with the WizardOptionID.

+----------------------------------+
| WizardOptionID | Answers         |
+----------------------------------+
| Option1        | 1,2,1,2,3,1,1,1 |
| Option2        | 2,1,2,2,1,2,1,1 |
| Option3        | 2,2,2,1,3,1,1,2 |
+----------------------------------+

Not sure if that is the correct way to do this. Please let me know if more information is needed.

  • 2
    No, (comma) delimited string lists are almost always the complete opposite of "ideal". Consider instead having a table with the columns WizardOptionID and AnswerID in which you have a row for every answer needed to lead to an option. – sticky bit Jul 12 '18 at 20:40
  • @stickybit Thanks for the suggestion! I'll give it a try instead of just outputting all of the answer ids as a column value. – st_user Jul 13 '18 at 19:38
0

Based on the complexity of your wizard, you may face here two possible scenarios. A wizard is nothing else but a paged form. 1. Your wizard has always the same amount of answers 1.a. The questions are always the same. 2.b. The questions may be different (opting between one payment method or the other, and thus the answer might be different) 2. Your wizard may have different amounts of questions, eg. you have a groups of answers that might be enabled or disabled.

So let's analyze case by case. 1.a That's easy, you have questions Q1, Q2, Q3, Q4... And you can simply store the answers in the same order as a simple series, as in your example. 1.b. This is a bit more complex, since storing simply the answers is not enough, as the questions are not the same. However, since we know the amount of answers is always the same you can create a table with as many columns as possible answers answers, adding a special columns to identify ambivalent questions. So the structure would be something like

CREATE TABLE FormResults (
A1 VARCHAR(50), -- eg. Name
A2 VARCHAR(50), -- eg. Surname
Q3 INTEGER, -- Q3 is the Payment method chosen, can be PayPal, credit card, bank transfer
A3 VARCHAR(50), -- This answer can be the email for PP, the card number, the bank account... corresponding to the Q3 chosen.
A4 DECIMAL(4,2) -- e.g. The amount
);

The benefit of this solution is that your table will be quite compact with nulls only when an answer is optional.

For the case 2, if the number of answers is variable that's the most complicated case. The easiest way to solve this is simply to create a table with columns for all possible answers. However depending on your wizard this may result in a table plenty of NULL values. If this is the case, and if this is a problem in your RDBMS, then you may create a table like

CREATE TABLE FormResults (
AnswerSetId INTEGER,
QuestionId INTEGER,
AnswerValue VARCHAR(50)
);

Being the PK (AnswerSetId, QuestionId)

Here you may find that different answer sets will have different number of rows in this table, depending on the choices of the user. The good thing of this solution is that it creates a very compact and easily indexable table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.