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.

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.