sql - Table design for questionaire -

-

we working on application ask users various questions, these questions can have branches, don't run in straight order. example, if below structure of questions , can flow to:

enter image description here

question 2 has branch if user answers yes move 3 , end @ 7, otherwise work our way down 4,5,6,7. have come table design wanted feedback if design work or if there better way structure this.

there question table have id (key) , text columns. tree represented as:

questionid | previousid | nextid | branch condition 1                0            2      null 2                1            3      yes 2                1            4      no 3                2            7      null 4                2            5      null 5                4            6      null 7                3            0      null 7                6            0      null

the way work, once question answered system check current question's branch condition is, if null move nextid. otherwise find answer user selected , go associated nextid. expect y/n questions branch.

the previousid i've kept now, in case users ability go , retake question, want move forward.

if it's yes/no questions may have different next question, suggest adding 2 columns questions table specify next question id when answer yes (or default, when it's not yes/no question) , next question id when answer no. keeping previous answer id seems me redundant, since can keep in memory entire questions path in application itself.

however, if sometime in future going have multiple choice questions, , next question depend on answer (i.e category , sub categories), suggest add answers table well, , keep column next question id both in questions table , in answers table.
if next question id not dependent on answer, keep in questions table. if dependent on answer, put null in next question id column of questions table, , keep next question id in answers table. provide maximum flexibility , simple enough data structure.


Comments

Post a Comment

Popular posts from this blog

PHP DOM loadHTML() method unusual warning -

-
i had following code works fine on localhost: $document = new domdocument(); $document->loadhtml($p_result); $form = $document->getelementsbytagname('form')->item(0); // code continues using $form variable after same code updated on outside server, loadhtml() failed , issued warning. warning: domdocument::loadhtml() expects parameter 1 valid path, string given in path/name/to/script.php returned null instead of object code pretty gets fatal error. note contents of $p_result absolutely same on outside server , on localhost. but why displays kind of warning , why not work? doesn't loadhtml() expects argument 1 string in first place? why method expects parameter 1 valid path ? just make clear i'm not calling loadhtmlfile() , i'm calling loadhtml() . thanks. you're affected one of php bugs . issue present in php 5.6.8 , 5.6.9. have affected php version on server, , bug-free version on localhost. the bug forbids null
Read more

python - How to create jsonb index using GIN on SQLAlchemy? -

-
here's current code creating index jsonb. index("mytable_data_idx_id_key", mytable.data['id'].astext, postgresql_using='gin') but got error. sqlalchemy.exc.programmingerror: (psycopg2.programmingerror) data type text has no default operator class access method "gin" hint: must specify operator class index or define default operator class data type. [sql: "create index event_data_idx_id_key on event using gin ((data ->> 'id'))"] is there way create index on sqlalchemy? the postgresql specific sqlalchemy docs @ http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#operator-classes mention postgresql_ops dictionary provide "operator class" used postgresql, , provide example illustrating use: index('my_index', my_table.c.id, my_table.c.data, postgresql_ops={ 'data': 'text_pattern_ops',
Read more

c# - TransactionScope not rolling back although no complete() is called -

-
i'm using transactionscope rollback transaction fail bool errorreported = false; action<importerrorlog> newerrorcallback = e => { errorreported = true; errorcallback(e); }; using (var transaction = new transactionscope()) { foreach (importtaskdefinition task in taskdefinition) { loader.load(streamfile, newerrorcallback, task.destinationtable, processingtaskid); } if (!errorreported) transaction.complete(); } i'm sure there no transactionscope started ahead or after code. i'm using entity framework insert in db. regardless state of errorreported transaction never rolled in case of error. what missing ? transactionscope sets transaction.current . that's does. wants transacted must @ property. i believe ef each time connection opened reason. probably, connection open when scope installed. open connection inside of scope or enlist manually. ef has nasty "design decision": default opens
Read more