Sql-server – Mapping 5 columns to 1 (ID, dimension) table without 5 joins

join;physical-designsql servertable

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.

Best 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.

Related Solutions

Postgresql – Postgres planer JOIN removal

If you're testing with 50,000 rows in data_dynamic, I'd expect your inner join to return 50,000 rows. It seems to be returning about 10,000. There's a foreign key constraint—this seems unusual to me.

I loaded a bunch of random data, and ran this.

analyze data_dynamic;
analyze data_static;
explain analyze 
select count(d.id) from data_static s inner join data_dynamic d on s.id = d.id;

And here's what my box returned. (PostgreSQL 9.0.2)

Aggregate  (cost=14941.95..14941.96 rows=1 width=4) (actual time=477.633..477.633 rows=1 loops=1)
  ->  Merge Join  (cost=8409.72..14816.94 rows=50000 width=4) (actual time=237.807..456.682 rows=50000 loops=1)
        Merge Cond: (s.id = d.id)
        ->  Index Scan using data_static_pkey on data_static s  (cost=0.00..91190.58 rows=3000000 width=4) (actual time=0.016..195.279 rows=350001 loops=1)
        ->  Index Scan using data_dynamic_pkey on data_dynamic d  (cost=0.00..2342.23 rows=50000 width=4) (actual time=0.010..60.933 rows=50000 loops=1)
Total runtime: 477.706 ms

So it looks like the query planner is capable of coming up with a good plan for these tables and this quantity of data.

Does this mean your problem is either with hardware or with PostgreSQL configuration? I'm not sure it's that simple.

Later . . .

I was right; it's both simpler and not that simple.

Based on these snippets from PostgreSQL mailing lists, I'm going to say that the query planner simply doesn't yet support removing inner joins.

From the pgsql-performance mailing list, 02-Mar-2011

The planner doesn't currently make any deductions whatsoever from the presence of a foreign key constraint; and even if it did, I'm not sure that this would help it decide that a join order constraint could safely be dropped.

And from pgsql-hackers, 12-Dec-2010

I'm not going to argue that careful analysis isn't needed before doing something like this - and, in particular, if we ever get inner-join removal, which I'm still hoping to do at some point, . . .

Having trouble with Merge Join with SSIS

Normally, I'd say check your data types and or casing but if the lookup works, then that doesn't seem as probable. Safe to assume the lookup would be against the 7.8M row table b? Check your metadata anyway for the 4 keys on both inputs, maybe there's a length discrepancy

Related Question