Database design of the questionnaire survey system based on SQLite

Graduate design wrote an Android-based survey system, complete only the offline section, in order to store some local data designed this database. Later, a classmate of Android development told me that Android projects generally do not store large amounts of data locally. And before writing graduation design I almost did not contact the development of Android, in order to graduate design, also had to bite the bullet to do so, as for the source code is not posted out of shame. Here is the main introduction of the database design bar. Hope and interested people to discuss the study, I am interested in the database, although the work done now is not related to the database.

First of all, the questionnaire system has to find a way to store the questionnaire, my practice is to use code to parse the text organized according to certain rules, and then inserted into the database. I built three tables to store the questionnaire.

Title table, used to store the title of the questionnaire, descriptive information and the creation time of the questionnaire. has a unique identity ID. The table statement is as follows:

CREATE TABLE [title] (
[t_id] integer PRIMARY KEY autoincrement,
[T_title] varchar (+) is not NULL UNIQUE,
[T_describe] TEXT,
[T_time] DATETIME not NULL);

The results are as follows:

Question table, used to store the questions in the questionnaire and the type of the problem (basic questions have a single choice, multi-choice, judgment, blank questions). and a unique ID and a foreign key ID for the reference to the title table, the following are the table statements:

CREATE TABLE [Question] (
[q_id] integer PRIMARY KEY autoincrement,
[t_id] Integer not NULL REFERENCES [title] ([t_id]) on DELETE CASCADE,
[Q_context] Text not NULL,
[Q_type] integer not NULL,
UNIQUE ([t_id], [Q_context]));

The results are as follows:

Item table, which is used to store the question option in the questionnaire, a unique identification ID, referring to the foreign key ID of the title table, referring to the foreign key ID of the question table. The table statement is as follows:

CREATE TABLE [item] (
[i_id] integer PRIMARY KEY autoincrement,
[q_id] Integer not NULL REFERENCES [question] ([q_id]) on DELETE CASCADE,
[t_id] Integer not NULL REFERENCES [title] ([t_id]) on DELETE CASCADE,
[I_context] Text not NULL,
UNIQUE ([q_id], [I_context]));

The results are as follows:

The above three tables can basically store a common questionnaire, encountered a picture problem or option, my practice is to use the "-" symbol in the question content or option content to connect the name of a picture, and then store the picture under the specified folder, the folder under the code to load the picture. If one of the options in the problem option is "other" or "other," the code considers the issue to be a single-choice or multiple-choice question with a blank fill. This type of question is expanded to six.

With the questionnaire, I also need to store the survey results. To achieve this, I created two additional tables.

The Questionaire table stores the time the questionnaire was created, whether the survey was completed, and whether the content was uploaded. There is also an identity ID, and a foreign key ID that references the title table, which consists of a federated primary key. The table statement is as follows:

CREATE TABLE [Questionaire] (
[qn_id] integer not NULL,
[t_id] Integer not NULL REFERENCES [title] ([t_id]) on DELETE CASCADE,
[Qn_time] DATETIME not NULL,
[qn_completed] integer not NULL,
[qn_up] integer not NULL,
CONSTRAINT [Sqlite_autoindex_questionaire_1] PRIMARY KEY ([qn_id], [t_id]));

The results are as follows:

Answer table, which is used to store survey results of questionnaires. And referring to the foreign key ID of the question table, reference the foreign key ID of the item table, referencing the federated foreign Key ID of the Qustionaire table. The table statement is as follows:

CREATE TABLE [Answer] (
[q_id] Integer not NULL REFERENCES [question] ([q_id]) on DELETE CASCADE,
[t_id] integer not NULL,
[qn_id] integer not NULL,
[i_id] Integer REFERENCES [item] ([I_ID]) on the DELETE CASCADE,
[Context] text,
FOREIGN KEY ([t_id], [qn_id]) REFERENCES [Questionaire] ([t_id], [qn_id]) on DELETE CASCADE);

The results are as follows:

Put some more SQL statements:

Select T_id,t_title,t_describe,t_time from the title order by t_id ASC to find the questionnaire title.

Select COUNT (*) as C from questionaire where t_id =?, '? ' Number is populated with the ID of the title table, which is used to count the following questionnaires.

Select Q_id,q_context,q_type from question where t_id =? ORDER BY q_id ASC, '? ' Number is populated with the ID of the title table, which is used to find all the problem content in the questionnaire.

Select Qn_id,qn_time,qn_completed,qn_up from questionaire where t_id =? ORDER BY qn_id ASC, '? ' Number is populated with the ID of the title table and is used to find information about all questionnaires corresponding to the questionnaire.

Select I_id,i_context from item where q_id =? and t_id =? ORDER BY i_id ASC, '? ' Numbers are populated with the identity ID of the question table and the ID of the title table, which is used to find all the options for a questionnaire corresponding to the following question.

Finally, the success of graduation, and I am now engaged in C + + development, the industry or smattering, I hope Daniel can point to me a direction, good to continue efforts!

Database design of the questionnaire survey system based on SQLite