Database architecture regarding configurator with steps and options

Question

I have a database architecture question for a products configurator.

The user must answer to some successive questions (steps). Each step has several options. The options for one step depend on the selection from the previous step. For each step, only one option can be selected.

So, for example:

1. What do you want to wear today? a) Shoes b) Slippers

2. (a) Should the shoes have laces? a) Yes b) No

3. (b) What color should the slippers be in? a) Red b) Transparent

What I have so far is:

###
# Table structure for table 'filterstep'
###
CREATE TABLE filterstep (
    id int(11) NOT NULL auto_increment,
    title varchar(255) DEFAULT '' NOT NULL,
    parent_step varchar(255) DEFAULT '' NOT NULL,
    options int(11) DEFAULT '0' NOT NULL,
    PRIMARY KEY (uid)
);

###
# Table structure for table 'filteroption'
###
CREATE TABLE filteroption (
    id int(11) NOT NULL auto_increment,
    title varchar(255) DEFAULT '' NOT NULL,
    seo_alias varchar(255) DEFAULT '' NOT NULL,
    icon int(11) unsigned NOT NULL default '0',
    image int(11) unsigned NOT NULL default '0',
    description text NOT NULL,
    step int(11) unsigned NOT NULL default '0',
    PRIMARY KEY (uid)
);

This allows me to have options for each step and also defines the steps order, but I cannot figure what would be the best way to connect an option to the previously selected option.

Answer 1

You can have a table specifically for keeping all the questions and the immediately subordinate questions, while keeping in another table all the options for each question and the target question associated to that selection, like in the diagram below:

So, the option table have two relationships with the step table: the Step field for the subordination itself (ie, the options to be selected by the user whenever he/she tries to answer the question) and the Target field to rule what question should be presented next.

This way you can add records like this:

To enforce the option target consistency, you should create your administrative UI to just allow the selection of steps associated with the parent step itself, or a NULL value which would finish the user input.