Database
Lighthouse Studio saves all respondent data to a database. Both MySQL and Microsoft's SQL databases are supported.
If you are hosting Lighthouse surveys on your own web server, your database administrator will need to set up a database that Lighthouse Studio can use. You will need a database name and a database user name and password to access it. Take these settings and enter them into Lighthouse Studio under Field | Hosting Management.
When uploading a study to the web server, Lighthouse Studio uploads various files and folders, one of which is the "db_setup" folder (located inside of the admin folder). This folder contains three main files:
STUDYNAME_layout.cgi
STUDYNAME_questionnaire.cgi
STUDYNAME_pwds.cgi (only present if you have identifiers associated with your study)
These files contain the information necessary to set up the database. The layout.cgi file contains information about every data field in the study. The pwds.cgi file contains the respondent identifiers that need to be loaded in the study. The questionnaire.cgi file contains all of the survey text and logic. After the database is set up this file becomes the qst.cgi file in the admin folder.
The Auto Upload feature of Lighthouse Studio uploads these files and creates the database tables necessary to run the survey. If you need to make changes Auto Upload applies the changes to the survey (including changing the database if necessary) automatically.
If you are manually uploading a survey, after you have uploaded the contents of the Web Upload folder, make sure to log into the Admin Module. The first time you log into the Admin Module the survey database tables are created. Each time you upload changes make sure to click "Apply Changes" in the Admin Module. No questionnaire or identifier changes are made until you click "Apply Changes".
During the period that your survey is being updated there is a small chance that active respondents will see the Survey Paused error message. This message by default says:
"The survey is currently being updated. Please try again in a few seconds."
They are able to click the Next button at the bottom of the page to continue where they left off.
Appropriate Perl modules must be installed on your web server to allow Lighthouse Studio's Perl scripts to communicate with the database.
See http://www.sawtoothsoftware.com/server-setup for details.
Database Tables Created by Lighthouse Studio
Each study creates various tables in the database that you have specified in the Server Database Settings. Each table name has the study name as a prefix.
STUDYNAME_info
This table contains miscellaneous information about the study.
STUDYNAME_data1
This table contains most of the respondent data. Each row in the table contains the data for an individual respondent. The first field of this row is the Internal Respondent Number. The Respondent Number is a unique number that identifies each respondent.
Since databases have limits on how many fields (or columns) each row can have, Lighthouse Studio might store additional respondent data in additional data tables (data2, data3, etc). Rows in multiple data tables are linked together by the Internal Respondent Number. MySQL can hold about 1,600 columns per table. Microsoft SQL can hold about 1,000.
STUDYNAME_map
The map table provides a way to look up which data field is in which data table. Each row in the map table contains a data table number and all of the data fields that can be found in that data table.
STUDYNAME_history
The history table stores the respondent's history, enabling the respondent to back up in the survey.
STUDYNAME_clists
Constructed lists are stored here.
STUDYNAME_passwords
If you have defined respondent identifiers in your study this table is created. Contains all of the respondent identifiers defined in the current study.
STUDYNAME_quotas
This table is created and used if you have quotas defined in your study. The quotas table keeps track of quota status including all of current quota cell limit counts.
STUDYNAME_saved_reports
This table is created and used if you have saved any data reports in the Admin Module. Contains saved report settings.
STUDYNAME_admin_log
This table keeps track of Admin Module activity. Actions such as login, edit data, and delete data are recorded.
STUDYNAME_aca_avg_imp
This table is only created if using ACA with "Use Group Means for Prior Importances" selected. Contains ACA Group Means information.
STUDYNAME_url_restore
From time to time the survey URL is used to recover a respondent's session and to figure out the Internal Respondent Number etc. This table stores information necessary to restore a respondent session if necessary.
STUDYNAME_design_log
Various types of surveys (i.e. ACBC surveys) require designs to be generated as respondents take the survey. This table stores any messages that might be generated while creating the design.
Because each database table has the studyname as a prefix, a single database can store multiple Lighthouse studies. If you want to have two studies with the same name on your server, then you'll need to set up a separate database for each study (to avoid conflicts).
NOTE:
If mid-survey you remove a question, or change its name, Lighthouse Studio renames the old question to have a prefix of "_OLD" in front of it. The prior data collected under the old name are still in the database but will not be available for download or export etc. The information could be extracted manually. A survey Reset will remove all _OLD data columns.
Utilities such as phpMyAdmin (http://www.phpmyadmin.net) can be very useful to administer MySQL databases. Tools such as these allow you to manually interact with the survey data.
