Mapping 5 columns to 1 (ID, dimension) table without 5 joins?

Posted on

Question :

I’m using survey monkey’s API to dump survey data into a SQL data warehouse.

So I’m dumping response data into a table ‘resonses’ thusly:

survey_id, Q1, Q2, Q3, Q4, Q5

survey3944, 848948344, 430984384, 43094839, 238094983, 2380284

Then I have a dimensional table ‘answer_key’ that has the id/ text combos. Such as

ID, text

848948344, 5 - very satisfied

I’m wondering how I can join the 5 answer columns above into meaningful text (or at least the integer) without doing 5 joins.

I mean say I have the table ‘responses’ and the table ‘answer_key’.

My initial thought would be go through the motion:

Select r.survey_id, a.text, a2.text, a3.text, a4.text, a5.text
from responses r
inner join answer_key a on r.q1 = a.id
inner join answer_key a2 on r.q2 = a2.id
inner join answer_key a3 on r.q3 = a3.id
inner join answer_key a4 on r.q4 = a4.id
inner join answer_key a5 on r.q5 = a5.id

Is that the best way to accomplish this? Just wondering. Especially if the survey is expanded to 10 questions, or what not.

I realize I can have a row for each question response rather than each survey for the table structure — that’s a possibility but I think it’s a bit more difficult given the structure of the JSON, but maybe it’s more logical.

Answer :

Normalise the table responses so there is 1 question per row. Then you are also not limited to a number of questions per survey.

Leave a Reply

Your email address will not be published.