Survey creation fun

I ended up committing to a drupal based project my SO wants done (isn't that always the case). Part of it's specifications is to include a survey for users.

On initial thought I had grand ideas of creating a module that would allow for arbitrary surveys to be created (as I'm not particularly impressed with the current modules available), and I may yet attempt that.

However, the problem I'm running into is how to best capture this information in a relational database. I remember briefly considering this idea back in the day, and have a feeling I was so bothered by the implications that I largely erased the ill thoughts from my memory.

I found a question @stackoverflow that covers this, unfortunately there haven't been any decent solutions as of yet.

My first instinct when faced with this sort of thing is to map out a normalized database structure. At a high level this seems easy, as you basically want a Form, you want Questions to attach to your form, and you want Answers.

form
  id
  title
  date

question
  id
  form_id
  title

Looks good so far, except well, you know your questions are going to be in the format of HTML form elements, so you have a textfield perhaps, capturing information like an email address, or a radio button, checkbox, etc... Since HTML form elements are pretty standard, I think we can avoid a "type" table and just use an enum lookup.

question
  id
  form_id
  type_enum
  title

Ok, well now, we can see our field types have different requirements. A text type input should have "maxlength", "size" defined, while a radio button will have x number of choices associated with it, ditto with checkboxes and a select field.

So hmm, maybe we should make a separate table for each field type then, since the options vary.

input_text_options
  question_id
  size
  maxlength

Ugh, how do we capture x number of options though. Another table?

checkbox_options
  question_id
  option

Ok so mental note being if our question is of a standard text input type, we need to grab that information from "input_text_options". If it's some sort of checkbox, radio button, select/multiselect, we'd have to grab multiple options from their respective tables for each question. The queries are starting to scare me!

At this point I'm strongly considering denormalizing a bit, and just storing a serialized array of options perhaps...

questions
  id
  form_id
  title
  type_enum
  options

Well that makes the database structure cleaner, at the cost of some normalization.

Anyhow, I haven't decided which methodology to adopt, I suppose we'll see what my reporting queries look like, to gauge whether more normalization would make things easier down the road, or if I can get away with "cheating" a bit here.