Design question - data structure for questionnaire

I've done something like this a few different times - and at least one of those times, the questionnaires were actually built in the target system by users (but then just exported to a document and filled out later in hard-copy).

You're definitely correct in that you would be best off avoiding a huge CDT - it would give you very little flexibility and almost no benefits other than slightly higher simplicity in data structure.

Will all questions be the same type?  I.E. will every question (always) consist of 4 multiple-choice answers, or would some be T/F, "fill in the blank", etc?  This will come into play when figuring out how to structure your question data (and answer data).

At a minimum you're probably looking at:

• A table to hold every individual unique Questionnaire
• A table to hold each Questionnaire's Questions (each row would contain a foreign key to the Questionnaire ID)
• A table to hold each Question's Answers (each row would contain a foreign key to the Question ID)
• A table to hold each individual "Response", i.e. the data that gets created when a user actually completes one in-process; would key to the Questionnaire ID, "initiated by" username and initiated date, etc
• A table to hold each separate answer chosen for each specific "Response" - each row would contain the Question ID and selected Answer ID, the username of the specific user who answered that question, date answered, etc.

Handle all these and you'll be up to a good start.  The data structure becomes a little complicated but is pretty powerful as well as flexible.

Thanks Mike, your answer is very helpful :)

The questions will have different structure types (boolean, multiple choice, free text, etc). I will have to figure out how to structure the answers. The simplest way would be to store every answer as a string, but I Need to think about it.

The way I did this (and there may be better ways) was to make every Question CDT have a "type" i.e. multiple choice, t/f, short answer, etc... and every question type with predetermined answers (multiple choice and t/f in this case) will get "answer" entries in the answers table -- in the case of t/f, they'd just be "true" and "false", but for multiple choice ones, there could be two-or-many answer entries (theoretically unlimited). 

Each Answer CDT entry has an id, a "label" (string) storing the answer's text, an "active flag" which initially is set to TRUE but we set to FALSE if the user "deletes" that answer, and an "order ID" which determines the ordering of the answers under that particular question (so we don't need to rely on them having been originally inserted in the same order that we want them sorted, in the long run).  (BTW, most of these also apply to the Question CDT.)

In determining the complexity you want to bake in, you'll also need to consider whether you will need to build an in-Appian questionnaire editor, either initially or potentially in the future.  If the questionnaire data will only ever be directly created by devs in the database and only edited that way too, then you can probably use a simpler design than if you'd want to do any of this within an Appian GUI.

I've built this kind of thing in the past. I design one table to hold the questions, along with any meta-data about the type of question (e.g. is the answer to the question a 'Yes/No' choice, or a choice of 3 values, or some text that needs to be captured etc); and then a table to hold the answers to the questions (along with a pointer to the question type in the first table). And the code in the SAIL interface essentially dynamically builds the interface based upon the content in the first table, and writes out the answers to an array that gets written to the second table. In this way you can not worry about adding or removing questions from the set because you can add or deprecate rows from the first table and the interface will simply dynamically render the interface based upon what it can find at run-time. You'll find the CDTs are much smaller in terms of their attributes, but the code does become trickier do deliver. But once implemented you'll only need to make changes to the design if you have to add a different type of question (i.e. one that requires a different answer type to the ones you've already delivered).

Thanks Stewart, that is my view as well. I feel much more comfortable now with the design.