Have an idea?

Visit Sawtooth Software Feedback to share your ideas on how we can improve our products.

"Max Field per Database Table": Max. 300?

Hi dear @all,

i use LAMP as hosting  with MySQL as Database and try to set "Max Fields per Database Table" on "Survey Settings"-> Advanced-Tab to 600. Unfortunately comes an error: "Error: Survey Settings -  The value  for "Max Fields per Database Table" must be between 80 and 300". Is it possible to set this Value to higher than 300?

My Version is Lighthouse Studio 9.8.0


Thank You for your Answers,

Alex
asked Jan 18 by Alex E. (120 points)

1 Answer

0 votes
Hi Alex,

As a general rule, we discourage people from setting the "Max Fields per Database Table" option to higher values. Database tables can only hold a certain amount of information in them. As such, the software will split the collected data fields across multiple tables so that more information can be collected in the survey than a single table could handle.

The "Max Fields per Database Table" option simple lets the software know where to start storing other fields on a new table.

For example, if you are collecting 600 data points in the survey and "Max Fields per Database Table" is set to 300, the data will be stored across two tables:
Table 1 will contain 300 data fields, and Table 2 will contain 300.

If you set "Max Fields per Database Table" to 100, the data will be stored across six tables, with each table containing 100 data fields.

As such, increasing the "Max Fields per Database Table" option to 600 will try to put all the data fields into a single database table. This can cause errors because the database engine may not be able to store that much data in a single table - especially if the data fields you are collecting are larger (such as open-end text responses).

From a usability standpoint, there is very little difference this option will make on the survey itself; when you download the data, the software will join all the data from across the database tables into a single .csv and present it all together. From a database performance perspective, spreading the data across multiple tables does mean querying that data requires more transactions, or more complex transactions, but to this point, we have never seen this impact a survey negatively enough to be anywhere close to significant.

Occasionally, when uploading a survey to a server, a user might see an error from the database that has something to do with data fields or capacity or tables being unable to be created. The solution to this is almost always decreasing the "Max Fields per Database Table" option, not increasing it.

So, in short: No, the software does not allow for a higher "Max Fields per Database Table" setting, but it is by design. There are very few reasons why it would ever be advantageous to do so.
answered Jan 20 by lucien Sawtooth Software, Inc. (300 points)
HI lucien,

Thank you for your quick answer:  it was my intention, to evaluate the performance of my study with 21000 fields -> ~ 70 tables. But: no is no :)  It was not 100 %  clear for me, when I read it in Help-Topics.

Good news: it is possible to run such a survey :)  

Best regards,

Alex
...