Database Design for a Wizard’s Output

Definitely do not hard code your questionnaire. Use a relational database or xml files. I propose the following tables

• Questionnaire: General description of questionnaire. Title, name of survey, questionnaire release date, version, and so on.

• Section: The sections a questionnaire is made up. Number of the section, section title, description.

• Question: The questions belonging to a section. Number of the question, question text, description, question type (text, multiple choice, etc.).

• Question_Choice: The possible answers belonging to a question corresponding to the single checkboxes, radio buttons, and so on. Text of the choice, choice number, order.

• Respondent: The persons answering the questions. Personal data, user number.

• Interview: Interviews or tests or surveys (dependent on the nature of the questionnaire) belonging to one respondent and one questionnaire. If a respondent can always answer only one questionnaire (or if the survey is anonymous), this table is obsolete and can be merged with the Respondent table. Interview date (or test date or survey date), interviewer (if it applies).

• Answer: Answers belonging to one interview (or respondent, see above) and one question. Answer text (for text type questions), choice (for radio buttons).

• Answer_Choice: Choices belonging to one Answer and one Question_Choice when multiple choices can be checked.

This is a very normalized approach; however, you could decide to concatenate choices into one string or to store them as bit pattern or simplify it in some other way depending on your needs.

First, I don't see any problem with doing this in the db. In fact we are moving this direction with LedgerSMB. The big caution is that you want to be prepared for business rule changes. Of course your old partnumbers will be stable. but SKU's are stable for a long time usually unless you are Frito Lay and then it just makes trouble for everyone else (they change UPC's it seems every time a word on their packaging changes).

What we've done in LedgerSMB is to essentially store a template, and then we preprocess and increment. That isn't quite what you are looking at doing, but it may help to see how we have looked at this in the past and where we are going.

Our templates look something like:

<?lsmb field_name ?>somechars<?lsmb another_field?>0001102

Leading zeros are preserved on increment. Inside the db, we have a stored procedure which increments the number, keeping leading zeros and passes the rest up to the application. I don't see how we can do full preprocessing of this sort using invoice numbers and the like but for partnumbers the goal eventually is to move all that preprocessing into the database.

Now in your case, you probably want to have the parts tied together for easier management. I understand the arguments against this. I would suggest putting all these fields you want to aggregate in the table representing the parts. Then you can integrate these with a user defined function or something (that's how I'd do it in PostgreSQL) which you can use to populate a missing part number.

In this case your table structure will look very different:

product_group(id-- 0000-9999, etc)
fabric (id ....)
etc....
part (partnumber, productgroup_id, fabric_id, etc)

I don't see another sane way of doing it. You are going to have to track each of these things you want to use separately, put them in the parts table, and then generate your partnumber based on that.