Hi, guys. It would be great if LimeSurvey supported answer codes longer than 5 characters. I would suggest a much higher limit --say, 32 characters,-- to allow for meaningful answer codes that could go straight into the final database without needing to be substituted manually.

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

And with longer code : survey column number must be really lower …
Then : no …

Fully agree to Denis.

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.

that would have to be edited later.

This is really not a problem. Add a few lines to the syntax file of SPSS to recode or a small EXCEL macro.
Or you create a hidden question of type short text and store the date here by Expression Manager.

Joffm

Thanks for replying!

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)".

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: In your case: a four digit year is really not necessary; so you could use "YMMDD" which covers a ten years range.

Yep, that's exactly how I am doing it now...

As far as I know, the limit of columns is more somewhere around 1000, due to the the storage engines.
www.limesurvey.org/manual/General_FAQ#Wh...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.

Well, it was a raw estimation, because now the limit of columns is about 1570.

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

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)?

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.

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

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

The survey size is a tricky one. I find the current solution super simple, because each response has its own column, super clean, super clear, super easy to understand, but also a big limitation when it comes to surveys with a lot of columns necessary.

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.

Hi,
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

www.limesurvey.org/forum/design-issues/1...in-the-survey#166230

Here the findings are 1575 colums of VarChar(5), MyIsam.

Joffm

1240+ columns can be fine, or they can't. Unfortunately there is no way to say that the limit is exactly X columns, without know the questions, etc. But Joffm gave you a very clear example. But it doesn't mean that 1575 is the limit in all cases.

How will you notice that you run into this limit? I think you will only really know once you activate the survey and then get an error message. Maybe Denis knows more.

holch wrote: How will you notice that you run into this limit? I think you will only really know once you activate the survey and then get an error message.

Correct. The amount of questions is often more than it looks. There is a reason why LimeSurvey has questiontypes with answers and sub-questions. Subquestions demand more space. A single answer question has answers. A multiple choice question has subquestions.

As long as LimeSurvey carries on with the "wrong" database concept (Roadmap states "Refactor answer database to get rid of column limitations"), you will hit the wall with the database depending on your survey design on the range of "never" to "quite often". Even with a "simple" survey design you can hit the database limit. If you think about workarounds for looping and other features, you get to the limit even quicker.

jelo wrote: As long as LimeSurvey carries on with the "wrong" database concept...

So, what would be the "right" design? Basically, I can only see two viable options here:

1. Seamlessly creating overflow tables when the row size limit is reached.
2. Having a single, very simple answer table with one row per answer and the columns being participant-token, survey-id, survey-instance-id, question-id, [subquestion-id] and, finally, the value, which would be just a bit-bucket of a certain length. The value field, of course, would need to be interpreted based on the question type whenever the results are either displayed or exported. This kind of an architecture would clearly be more performance-intensive than the current one, but we are not talking about mega transaction volumes here, so I think it would be doable, and on the positive side, it's a very simple solution, and one that takes care of the row size limit problem once and for all.