- Posts: 103
- Thank you received: 2
Welcome to the LimeSurvey Community Forum
Ask the community, share ideas, and connect with other LimeSurvey users!
Allow longer answer codes (and question codes, too)
- oleggorfinkel
- Topic Author
- Offline
- Premium Member
Here is a typical scenario where such functionality would be needed: I want my respondents to select one of several courses from a drop-down box, but for each selection, what I actually want to store in the database is the starting date of the course (these would be unique, of course). I would like to be able to specify the starting date in the YYYY-MM-DD format as the answer code for each option, instead of a meaningless code, such as "A1", "A2", etc., that would have to be edited later.
Being able to use longer question codes would help, too, although for a different reason: in a survey with lots of questions that fall under different categories I like to use prefixes or suffixes to make it easier to remember the codes, and that makes me run out of room pretty quickly.
Cheers...
Oleg G.
- DenisChenu
- Offline
- LimeSurvey Community Team
- Posts: 13597
- Thank you received: 2487
Then : no …
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
- Joffm
- Offline
- LimeSurvey Community Team
- Posts: 12881
- Thank you received: 3965
Well, the subquestion code can be a long one, but the length of answer codes is limited to five.
Why? The answer codes are stored in the answer table. And the field type in the dataset is "VARCHAR(5)".
Because of the limitation of MySQL you would only be able to create about 230 columns if you change it to "VARCHAR(30)".
In your case: a four digit year is really not necessary; so you could use "YMMDD" which covers a ten years range.
Or you use a multiple question with limitation of answers to 1.
This is really not a problem. Add a few lines to the syntax file of SPSS to recode or a small EXCEL macro.that would have to be edited later.
Or you create a hidden question of type short text and store the date here by Expression Manager.
Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
- oleggorfinkel
- Topic Author
- Offline
- Premium Member
- Posts: 103
- Thank you received: 2
Mmm, yes, I understand that longer answer codes will reduce the column limit. I am a bit confused about the actual numbers you are citing, though. MySQL row size limit is 65,525 characters. Even if all the columns were 30 characters wide, we'd still be able to have over 2,000 of them. So, where does the 230-column limit come from? Now, if 30 characters might indeed be excessive, how about bumping the answer code length by just a little -- say, to VARCHAR(? That would already be quite a bit more helpful to a survey designer, and doesn't seem too costly in terms of space...Joffm wrote: The answer codes are stored in the answer table. And the field type in the dataset is "VARCHAR(5)".
Because of the limitation of MySQL you would only be able to create about 230 columns if you change it to "VARCHAR(30)".
Yep, that's exactly how I am doing it now...Joffm wrote: In your case: a four digit year is really not necessary; so you could use "YMMDD" which covers a ten years range.
Oleg G.
- holch
- Offline
- LimeSurvey Community Team
- Posts: 11639
- Thank you received: 2737
manual.limesurvey.org/General_FAQ#What_l...does_LimeSurvey_have ...
Unfortunately, depending on your survey design you can reach the limit of the database quite quickly. Limesurvey's database design isn't really made for complicated and long surveys.
I just recently had a survey with 5 (!) questions, that I could not really run in Limesurvey. If you'd have your first look at the survey in Word, you'd say: easy peasy.
Then you dive into the details and things look totally different. Just 1 of the questions was a matrix of 50+ departments to be evaluated on a scale from 1-10 for 7 items. Of course not everyone would see every department, but still, they need to be there, because each person can have a individual set of departments to answer.
So only this question setup alone would require a minimum of 350 columns. I am not going too much into more details here, but you see that a limit of 1000 columns is easily reached without having a survey with an absurd length.
I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.
- Joffm
- Offline
- LimeSurvey Community Team
- Posts: 12881
- Thank you received: 3965
I investigated, and I was able to create a table with more than 700, but less than 750 columns. each of VARCHAR(30) and storage engine "MyIsam". In "InnoDB" there is a limit of about 1000 columns.
I didn't go further to find the correct value.
As holch said, unfortunately this limit is reached very quickly (arrays, multipunch questions, and as top of all, array(number))
Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless
- oleggorfinkel
- Topic Author
- Offline
- Premium Member
- Posts: 103
- Thank you received: 2
Joffm wrote: ...unfortunately this limit is reached very quickly (arrays, multipunch questions, and as top of all, array(number))
Joffm
Since PostgreSQL practically doesn't have a row size limit, couldn't LimeSurvey be made to detect that it is being used and have an option for enabling longer answer codes just in that case (with adequate warning to the user, of course, as to the resulting loss of portability for the data)?
Oleg G.
- DenisChenu
- Offline
- LimeSurvey Community Team
- Posts: 13597
- Thank you received: 2487
Since you have only this update : it's easy to merge to master when needed.
One of the reason of this
1. 133 issue not assigned : bugs.limesurvey.org/search.php?project_i...atus=90&match_type=0
2. More issue assigned but not resolved : bugs.limesurvey.org/search.php?project_i...atus=90&match_type=0
Assistance on LimeSurvey forum and LimeSurvey core development are on my free time.
I'm not a LimeSurvey GmbH member, professional service on demand , plugin development .
I don't answer to private message.
- oleggorfinkel
- Topic Author
- Offline
- Premium Member
- Posts: 103
- Thank you received: 2
DenisChenu wrote: Maybe most easy is to create a fork of limesurvey with only this update.
Since you have only this update : it's easy to merge to master when needed.
Actually, after reading up a bit more on PostgreSQL, I am not sure anymore what its row size limit really is. I've seen confusing statements about this online, where on one hand it is said that a row can be as large as 1.6 TB, but on the other, that the number of columns is limited by how many will fit into the maximum allowed page size of 32 KB. I am not sure how to reconcile these two statements...
Oleg G.
- holch
- Offline
- LimeSurvey Community Team
- Posts: 11639
- Thank you received: 2737
Limesurvey would need a redesign of the database structure or at least an option to join two or more tables to allow more columns. But from what I understand, this is tricky and thus no one really wants to touch it, because you never know, what additional issues you create with this.
I answer at the LimeSurvey forum in my spare time, I'm not a LimeSurvey GmbH employee.
No support via private message.
- orvil
- Offline
- Premium Member
- Posts: 111
- Thank you received: 20
these answers unsettled me a little! I am working with a survey that has 1240+ colums as output for a long while without any problems. So I am not exactly shure about these limits (working with mariaDB).
Anyhow the problem of oleggorfinkel can be indeed solved, e.g. in Excel by a simple vlookup() command with a reference list.
Here my question to holch or denis: how do I recognise upcoming troubles with the DB's limits?
THX
Best regards/Beste Grüße,
O. Villani
- Joffm
- Offline
- LimeSurvey Community Team
- Posts: 12881
- Thank you received: 3965
Here the findings are 1575 colums of VarChar(5), MyIsam.
Joffm
Volunteers are not paid.
Not because they are worthless, but because they are priceless