# Storing data for frequently changing forms, how? **URL:** \ **Category:** Rails\ **Created:** [June 7, 2007, 8:33pm UTC](https://www.ruby-forum.com/t/storing-data-for-frequently-changing-forms-how/106247 "2007-06-07T20:33:18Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![d2kagw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/b5a626/32.png) [@d2kagw](https://www.ruby-forum.com/u/d2kagw)\ **Post date:** [June 7, 2007, 8:33pm UTC](https://www.ruby-forum.com/t/storing-data-for-frequently-changing-forms-how/106247/1 "2007-06-07T20:33:18Z")
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! ---
**Author:** ![d2kagw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/b5a626/32.png) [@d2kagw](https://www.ruby-forum.com/u/d2kagw)\ **Post date:** [June 7, 2007, 8:51pm UTC](https://www.ruby-forum.com/t/storing-data-for-frequently-changing-forms-how/106247/2 "2007-06-07T20:51:21Z")
On 6/7/07, Aaron W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 > **[Home - Blizzo](http://www.blizzo.com)** > > Insight. Culture. Passion. OUR STORY About Our Clients We recognize that our clients are the key element in our success and that we play an important part in helping build their business and brand. Whether our client is a small service business or a... ---
**Author:** ![d2kagw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/b5a626/32.png) [@d2kagw](https://www.ruby-forum.com/u/d2kagw)\ **Post date:** [June 7, 2007, 8:57pm UTC](https://www.ruby-forum.com/t/storing-data-for-frequently-changing-forms-how/106247/3 "2007-06-07T20:57:56Z")
Morgan Whitney wrote: > On 6/7/07, Aaron W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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](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? ---
**Author:** ![d2kagw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/b5a626/32.png) [@d2kagw](https://www.ruby-forum.com/u/d2kagw)\ **Post date:** [June 7, 2007, 9:28pm UTC](https://www.ruby-forum.com/t/storing-data-for-frequently-changing-forms-how/106247/4 "2007-06-07T21:28:24Z")
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 W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![d2kagw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/b5a626/32.png) [@d2kagw](https://www.ruby-forum.com/u/d2kagw)\ **Post date:** [June 8, 2007, 5:01pm UTC](https://www.ruby-forum.com/t/storing-data-for-frequently-changing-forms-how/106247/5 "2007-06-08T17:01:40Z")
I have been working on something similar. I kindof used the general schema of phpESP. > **[phpESP - php Easy Survey Package](https://sourceforge.net/projects/phpesp/)** > > Download phpESP - php Easy Survey Package for free. PHP script to let non-technical users create and administer surveys, gather results, view statistics. All managed online after database initialization. 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. ---
**Author:** ![d2kagw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/b5a626/32.png) [@d2kagw](https://www.ruby-forum.com/u/d2kagw)\ **Post date:** [June 8, 2007, 6:16pm UTC](https://www.ruby-forum.com/t/storing-data-for-frequently-changing-forms-how/106247/6 "2007-06-08T18:16:40Z")
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](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! ---
**Author:** ![d2kagw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/b5a626/32.png) [@d2kagw](https://www.ruby-forum.com/u/d2kagw)\ **Post date:** [June 7, 2007, 11:01pm UTC](https://www.ruby-forum.com/t/storing-data-for-frequently-changing-forms-how/106247/7 "2007-06-07T23:01:40Z")
On 6/7/07, Aaron W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 > **[Home - Blizzo](http://www.blizzo.com)** > > Insight. Culture. Passion. OUR STORY About Our Clients We recognize that our clients are the key element in our success and that we play an important part in helping build their business and brand. Whether our client is a small service business or a...