Forum: Ruby on Rails storing data for frequently changing forms, how?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
2a254d7fe9faa8a419404ef63a9cc63c?d=identicon&s=25 Aaron Wieberg (Guest)
on 2007-06-07 20:33
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!
75d66d19201c2e8193de9b62145d01a0?d=identicon&s=25 Morgan Whitney (Guest)
on 2007-06-07 20:51
(Received via mailing list)
On 6/7/07, Aaron Wieberg <rails-mailing-list@andreas-s.net> wrote:
> 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.

--
F. Morgan Whitney
http://www.blizzo.com
2a254d7fe9faa8a419404ef63a9cc63c?d=identicon&s=25 Aaron Wieberg (Guest)
on 2007-06-07 20:57
Morgan Whitney wrote:
> On 6/7/07, Aaron Wieberg <rails-mailing-list@andreas-s.net> wrote:
>> 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.
>
> --
> F. Morgan Whitney
> http://www.blizzo.com

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?
588ab1c0a5610a7e160a3b101abb91e6?d=identicon&s=25 MichaelLatta (Guest)
on 2007-06-07 21:28
(Received via mailing list)
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.

Michael


On Jun 7, 11:33 am, Aaron Wieberg <rails-mailing-l...@andreas-s.net>
75d66d19201c2e8193de9b62145d01a0?d=identicon&s=25 Morgan Whitney (Guest)
on 2007-06-07 23:01
(Received via mailing list)
On 6/7/07, Aaron Wieberg <rails-mailing-list@andreas-s.net> wrote:
> 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
http://www.blizzo.com
67c5ff1f6ed847f819edac651f82943f?d=identicon&s=25 peri (Guest)
on 2007-06-08 17:01
(Received via mailing list)
I have been working on something similar.  I kindof used the general
schema of phpESP.

http://sourceforge.net/projects/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.

Hope that sheds a little light.
2a254d7fe9faa8a419404ef63a9cc63c?d=identicon&s=25 Aaron Wieberg (Guest)
on 2007-06-08 18:16
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.
(http://en.wikipedia.org/wiki/Entity-Attribute-Value_model)

I was leaning that way and I'm starting to think it's the best way to
handle this problem.

thanks again!
This topic is locked and can not be replied to.