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! -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 6/7/07, Aaron Wieberg <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Morgan Whitney wrote:> On 6/7/07, Aaron Wieberg <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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.comSorry, 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? -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> 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! > > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 6/7/07, Aaron Wieberg <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. On Jun 7, 5:01 pm, "Morgan Whitney" <morganwhit...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 6/7/07, Aaron Wieberg <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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 Whitneyhttp://www.blizzo.com--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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! -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---