Implementing a '20 Questions'-like Wizard in a Database

Question

I am looking to implement a data-driven wizard with the question tree stored in an Oracle database. What is the best schema to use to make the database portion flexible (i.e. easy to add new paths of questions) without sacrificing too much in terms of performance?

Answer 1

You can build a tree structure using a foreign key that references the same table (a "pig's ear" relationship as it is often known). Then you can use the CONNECT BY syntax to traverse the tree. Here is a simple example:

SQL> create table qs
  2  ( q_id integer primary key
  3  , parent_q_id integer references qs
  4  , parent_q_answer varchar2(1)
  5  , q_text varchar2(100)
  6* );

Table created.

SQL> insert into qs values (1, null, null, 'Is it bigger than a person?');

1 row created.

SQL> insert into qs values (2, 1, 'Y', 'Does it have a long neck?');

1 row created.

SQL> insert into qs values (3, 2, 'Y', 'It is a giraffe');

1 row created.

SQL> insert into qs values (4, 2, 'N', 'It is an elephant');

1 row created.

SQL> insert into qs values (5, 1, 'N', 'Does it eat cheese?');

1 row created.

SQL> insert into qs values (6, 5, 'Y', 'It is a mouse');

1 row created.

SQL> insert into qs values (7, 5, 'N', 'It is a cat');

1 row created.

SQL> commit;

Commit complete.

SQL> select rpad('    ',level*4,' ')||parent_q_answer||': '||q_text
  2  from qs
  3  start with parent_q_id is null
  4  connect by prior q_id = parent_q_id;

RPAD('',LEVEL*4,'')||PARENT_Q_ANSWER||':'||Q_TEXT
------------------------------------------------------------------------------------------------------------------------------
    : Is it bigger than a person?
        Y: Does it have a long neck?
            Y: It is a giraffe
            N: It is an elephant
        N: Does it eat cheese?
            Y: It is a mouse
            N: It is a cat

7 rows selected.

Note how the special keyword LEVEL can be used to determine how far down the tree we are, which I have then used to indent the data to show the structure.