I’m working on a system that has many different forms (ie. Application,
Review, Amendment, etc.) and the questions on those forms are presented
to the user and they fill it out as necessary. The questions on those
forms change quite often, by adding new questions, removing old ones,
changing the data type of a question, etc.
I was thinking that rather than making changes in the database each time
a change to the form is made I could save all form data into a
serialized field. But I have concerns about the ability to run queries
in the data stored in the field and how that will affect reporting.
I was just wondering if anyone had a similar situation and how they
tackled it. Thanks for any input!
I was thinking that rather than making changes in the database each time
a change to the form is made I could save all form data into a
serialized field. But I have concerns about the ability to run queries
in the data stored in the field and how that will affect reporting.
I am unclear if you mean to say that you don’t want to alter the data
in the database each time, or the schema of the database each time.
If it is the schema, there are certainly good ways to make such a
system generic and be able to make such changes without it affecting
the schema.
I was thinking that rather than making changes in the database each time
a change to the form is made I could save all form data into a
serialized field. But I have concerns about the ability to run queries
in the data stored in the field and how that will affect reporting.
I am unclear if you mean to say that you don’t want to alter the data
in the database each time, or the schema of the database each time.
If it is the schema, there are certainly good ways to make such a
system generic and be able to make such changes without it affecting
the schema.
Sorry, yes I meant changes to the database schema. I inherited the
system I’m trying to write a replacement for and it seems like once a
week I have to add a column somewhere in the database so they can ask
another question on a form.
I would consider a start schema for what you suggest. You would have
a table that has 3 fields: form ID, question ID, and answer value.
Then you can add or remove questions all you like. Then you have a
form table for each set of answers that is joined to the answers
table. You can use sub-queries to make the whole thing look more like
a simple row in a table/view. You can also use subqueries to filter
the set of forms as you see fit to do queries on forms for specific
answers to questions.
I have been working on something similar. I kindof used the general
schema of phpESP.
It is a survey application, but sounds pretty much in line with what
you are doing.
In essence, each answer type (int,date, boolean, etc) is stored in its
own table. Creating the forms dynamically havent been that
bad…I’ll be curious when I get to the data retrieval for the
results.
Thanks for the input everyone. It seems like you guys are using the
same basic idea, and I’ve been looking into that too. I’ve seen
articles on it and they call it Entity-Attribute-Value Model, or EAV.
(Entity–attribute–value model - Wikipedia)
I was leaning that way and I’m starting to think it’s the best way to
handle this problem.
Sorry, yes I meant changes to the database schema. I inherited the
system I’m trying to write a replacement for and it seems like once a
week I have to add a column somewhere in the database so they can ask
another question on a form.
How would you go about making it generic?
I have done a similar schema in the past, and here is the schema I
would get started with:
forms:
id
name (friendly name for the form)
question_types:
id
question_type (suggestions: text, single-select, multiple-select, etc)
questions:
id
question_text (“please select your breed of llama”)
form_id
question_type_id
answer_options:
id
answer_text (“golden llama”)
question_id
answers:
id
answer_data
question_id
A note about how the answers work, if the answer type is one that
offers options, the available options are stored in answer_options.
in the answers table, if the answer is from a option list, it stores a
reference to the answer_option. However, if the answer_type is a text
one, it stores the text.
This is a bit of a hack, as you have to store the answer as a string,
even if its a reference to the an answer_option. There is probably a
more elegant way to handle that, but I am shooting from the hip here.
Good luck
–
F. Morgan Whitney
This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.
