Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: new meta_NNN table to work with data in survey_NNN tables

new meta_NNN table to work with data in survey_NNN tables 2 years 7 months ago #76035

  • TMSWhite
  • TMSWhite's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 759
  • Thank you received: 82
  • Karma: 36
We currently have suvey_NNN and tokens_NNN tables (where NNN is the survey id). How about also creating a meta_NNN table that would have one row per SGQA, and the following columns?:

id -- just a primary key
SGQA -- the < 1.92 naming for the variable
qcode -- the >= 1.92 naming for the variable
varname -- the name that would be exported to SPSS / R
qtype -- the question type
gid -- the group ID (for joining to groups table)
qid -- the question ID (for joining to the questions table)
gseq -- the 1.92 group sequence # (starting from 0, no gaps)
qseq -- the 1.92 question sequence # (starting from 0, no gaps)
qtext -- the text of the question
sqtext -- the text of the sub-question, if needed
rowdivid -- the JavaScript ID uses to control the visibility of a sub-question row.
ansList -- maybe - EM uses this to have normalized mapping of values to names for each answer, but may not be needed here (and if were here, should support all languages somehow).

Then, external reporting packages could join this table against survey_NNN in order to display human-readable names/information for the variables, and know enough meta data to properly handle the responses.

If so, it would be quite easy to add these tables. EM already knows all the needed variables names and attributes, so could be done as a simple function call upon activation.

Thoughts? (e.g. any objections)?

If we do this, what other columns should it have?

/Tom
The administrator has disabled public write access.

Re: new meta_NNN table to work with data in survey_NNN tables 2 years 7 months ago #76043

  • mark
  • mark's Avatar
  • OFFLINE
  • Expert Lime
  • Posts: 86
  • Thank you received: 1
  • Karma: 0
Tom this would be very useful. I sometimes make links to my LS mysql database for my clients and metadata like you describe would be fantastic. Hope you get moral & other forms of support from other users. If I could help I would but I'm a market researcher with very little sql experience.
Hoping there is demand for this.
Regards
Mark
The administrator has disabled public write access.

Re: new meta_NNN table to work with data in survey_NNN tables 2 years 7 months ago #76069

  • mdekker
  • mdekker's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 341
  • Thank you received: 68
  • Karma: 38
Store the language also in the row, as the question text depends on the language. Dual scale questions have 'headers' as a third dimension, coming from an attribute if I remember correctly. If you leave out the text elements you could skip the language and question / subquestion / header complications as the lookup can be done by the one who needs to know the information using joins.

This would be some extra overhead when changing a survey, but it could be created when the survey is activated and thus fixed yet it would mean a great improvement for any external tool trying to link to limesurvey. BTW, I would like sqga to be not the 'old naming' but the database fieldname. So that field can be used to map the responses table to their questions. And when we later change to maybe use the qcode as fieldname for storage, the external tools using that 'dbfieldname' don't need to be updated, only the contents of the field should change from sqga to qcode.

This could also be the output of a call to a SOAP/REST service.
---
Menno Dekker
The administrator has disabled public write access.

Re: new meta_NNN table to work with data in survey_NNN tables 2 years 7 months ago #76090

  • TMSWhite
  • TMSWhite's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 759
  • Thank you received: 82
  • Karma: 36
mdekker wrote:
Store the language also in the row, as the question text depends on the language. Dual scale questions have 'headers' as a third dimension, coming from an attribute if I remember correctly. If you leave out the text elements you could skip the language and question / subquestion / header complications as the lookup can be done by the one who needs to know the information using joins.

In retrospect, I tend to agree - rather than cluttering the meta_NNN table with question text that can be easily retrieved from the questions table via a JOIN. The only place it gets tricky, as you mention, is for dual scale and array type questions. If the JOINs to recreate the question and answer text is too difficult, then it may make sense to include them in the meta_NNN table, but I'd rather make it store largely unique information if possible so that we keep a reasonable level of normalization.
mdekker wrote:
This would be some extra overhead when changing a survey, but it could be created when the survey is activated and thus fixed

I was thinking this would only be created when the survey is activated, so should not be a problem when changing surveys. If a survey gets inactivated, changed, and re-activated, I'd envision just dropping and re-creating the meta_NNN table.
mdekker wrote:
BTW, I would like sqga to be not the 'old naming' but the database fieldname. So that field can be used to map the responses table to their questions. And when we later change to maybe use the qcode as fieldname for storage, the external tools using that 'dbfieldname' don't need to be updated, only the contents of the field should change from sqga to qcode.

Yes, I was thinking the sgqa code would be the database fieldname, since the main goal is to make it easy to join meta_NNN to survey_NNN on the sgqa/fieldname.

/Tom
The administrator has disabled public write access.

Re: new meta_NNN table to work with data in survey_NNN tables 2 years 7 months ago #76128

  • Fred
  • Fred's Avatar
  • OFFLINE
  • Gold Lime
  • Posts: 163
  • Thank you received: 5
  • Karma: 3
Tom, this is a great idea. We're dealing with this exact problem right now. Would your the rows in you table have a 1:1 relation and sequence between the columns in the responses table (ie, survey_NNN)?

How would things like "other" comments on multi-choice questions work? There would be a row for that in the meta table corresponding to the column in the survey_NNN table.

Finally, would you actually be able to do a SQL Join between the meta and the survey tables? The values in the meta table are the field names in the survey table.
The administrator has disabled public write access.

Re: new meta_NNN table to work with data in survey_NNN tables 2 years 7 months ago #76134

  • TMSWhite
  • TMSWhite's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 759
  • Thank you received: 82
  • Karma: 36
Fred-

Yes, I was envisioning a 1:1 mapping from rows in meta_NNN to data (SGQA) columns in survey_NNN, including all applicable "other", "comment", and "othercomment" fields.

True, you probably wouldn't want to actually use SQL JOIN between the tables. However, if you wanted to access human-readable variable names or other metadata from the survey_NNN table, you could read meta_NNN into an application so that you could use that information to create custom queries against the survey_NNN table.

If we had a meta_NNN table, it also begs the question of whether we could give the user the option to activate the survey_NNN table with either SGQA or qcode naming. That way if they wanted to use qcode names as the primary variable names in the survey_NNN table they could. EM could easily support that for run-time data collection, but I'm guessing that might require more significant changes to things like the survey reporting modules.
The administrator has disabled public write access.
The following user(s) said Thank You: Fred

Re: new meta_NNN table to work with data in survey_NNN tables 2 years 7 months ago #76138

  • Fred
  • Fred's Avatar
  • OFFLINE
  • Gold Lime
  • Posts: 163
  • Thank you received: 5
  • Karma: 3
Thanks Tom. I would definitely love a feature like this.

Personally, I would also like to have a converted survey_NNN table which was a long skinny table with a row for each question response and keyed to your meta_NNN table (rather the current big fat wide tables with a column for each response which aren't truly related to anything in SQL). And I would like to keep all survey, meta, and tokens data in three single tables rather than creating a new one for each survey.

But that is asking too much!
The administrator has disabled public write access.

Re: new meta_NNN table to work with data in survey_NNN tables 2 years 7 months ago #76139

  • TMSWhite
  • TMSWhite's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 759
  • Thank you received: 82
  • Karma: 36
Fred wrote:
Personally, I would also like to have a converted survey_NNN table which was a long skinny table with a row for each question response and keyed to your meta_NNN table (rather the current big fat wide tables with a column for each response which aren't truly related to anything in SQL). And I would like to keep all survey, meta, and tokens data in three single tables rather than creating a new one for each survey.

Fred-

That's actually how I store all my data in a survey tool I developed a decade ago (and which I've been porting into LimeSurvey). Details of the data model I used are here. It needs to be adapted to LimeSurvey, but I've hosted dozens of multi-hour instruments (averaging about 3000 questions) using that model, and collected responses from nearly a million users without any performance issues using such an Entity Attribute Value design.

/Tom
The administrator has disabled public write access.

Re: new meta_NNN table to work with data in survey_NNN tables 2 years 7 months ago #76152

  • Mazi
  • Mazi's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 5331
  • Thank you received: 296
  • Karma: 249
This is a great suggestion, Tom.

From ym experience I can say that lots of people who want to connect Limesurvey to other tools and especially read out the answer data from the lime_survey_12345 table are having problems connecting a certain column to the question and answer text. So having the lime_survey_12345 column heading in there would definitely make life easier.

Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)limesurvey.org'"
The administrator has disabled public write access.

Re: new meta_NNN table to work with data in survey_NNN tables 2 years 7 months ago #76207

  • DenisChenu
  • DenisChenu's Avatar
  • NOW ONLINE
  • Moderator Lime
  • Posts: 6451
  • Thank you received: 844
  • Karma: 249
Hello,

Something i don't understand.

Actually:

lime_questions:
qid,parent_qid,sid,gid,type,title,question,preg,help,other,mandatory,question_order,language,scale_id,same_default

You need a 1->N for each question for atribute.

qid is the questionID OR the subquestionID (see parent_qid)

You add inside:
  • varname -- the name that would be exported to SPSS / R : What to do if SPSS / R change and don't accept some caracter for 11.X version in some year ? I think varnale can be recalculated each time with : QID/GID/SID and title ( title is 'Code of the question').
  • SGQA : it's $SID."X".$GID."X".(($parent_qid==0) ? "" : $parent_qid).$QID
    ( some exeption for array: scale 1 / 2 : maybe it's the problem actually, then fix the "SGQA construction" before save it in database.).
  • rowdivid : i think it's a bad idea to have javabtSGQA in the=database. Next time we want to change the javascript name ( why not questionQID, same for the question id ? for example). We have to change all database. If you put in database, you can't change only code for construction.


Is the very important thing: if you fix something in the database, the you can't change after without a lot of modification in all installed database.

Denis

PS: it's just a quick look at the proposition.
Last Edit: 2 years 7 months ago by DenisChenu. Reason: ul li + PS
The administrator has disabled public write access.
  • Page:
  • 1
  • 2
Moderators: ITEd
Time to create page: 0.193 seconds
Donation Image