Hi, I want to write a rails application which is being used for querying a research database. It would be nice if the users (at least admin users) could add extra paramaters (variables) to the database, as time progresses. Mostly these will be stored in a table. What I want to know, is what is the feasibility of allowing the user to do the following: create a new table in the database, a foreign key link to that table in the database (from the main table), and have rails modify the model of corresponding to the main table, to reflect the newly added foreign key (has_a relationship). Your thoughts / opinions / ideas greately appreciated! Eddie -- Posted via http://www.ruby-forum.com/.
On 12/2/05, Eddie Stanley <eddiewould-wUU9E3n5/m4qAMOr+u8IRA@public.gmane.org> wrote:> > It would be nice if the users (at least admin > users) could add extra paramaters (variables) to the database, as time > progresses. Mostly these will be stored in a table.Not trying to be overly fussy but this seems a bit ambiguous. What are the other options implied by "mostly"? Also you say "a table". Does this mean all extra variables are to be stored in one single table. I don''t think this matchs with what you wrote after but if you do want to store them all in one table do you really want to do this... http://trac.vaillant.ca/store.rb/wiki/DbModelVirtualFields What I want to know, is what is the feasibility of allowing the user to> do the following: create a new table in the database,a foreign key link> to that table in the database (from the main table), and have rails > modify the model of corresponding to the main table, to reflect the > newly added foreign key (has_a relationship). > >I think you can do all this easily except the has_a which would be more involved. What is your example that you are working on? Peter _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
''ll be more specific about what I''m trying to achieve. The system will be used by med students, who basically enter patient/consultation details, come up a hypothesis (basically correlating symptoms and patient variables to a diagnosis) and then using the database to test the hypothesis, looking at the frequency with which the various diagnoses occur, given the set of conditions (symptoms present, patient variables). Depending on what the students decide to use as their hypothesis, they may wish to examine things which have not been looked at in previous years. Two which came up this year was ''Flu-Vaccine'' relating to when the patient last had a flu vaccine. The options for this are stored in a table with simply an id and a value - ''Never'', ''Unknown'', ''This year'', ''Previous''. This is table is linked to from the Consultations table via a foreign key. Likewise, there is a table ''SmokingStates'' with values ''Ex'', ''Current'', ''Never'' & Unknown. In the future, perhaps the course administrator would like add a variable ''Ethnicity''. This would need to be created as another table, with simply two fields - id and value. There would be rows in the table for the options for ethnicity, and we would need to add another field to the Consultations table, which is a foreign key referencing to the value for ethnicity. The SQL side of this isn''t going to be a problem - rails provides a way to execute raw SQL, but perhaps more of a challenege is going to be updating the rails models to reflect the new relationships. Surely someone has tackled this kind of problem before? Thanks very much for you ideas Eddie petermichaux wrote:> On 12/2/05, Eddie Stanley <eddiewould-wUU9E3n5/m4qAMOr+u8IRA@public.gmane.org> wrote: >> >> It would be nice if the users (at least admin >> users) could add extra paramaters (variables) to the database, as time >> progresses. Mostly these will be stored in a table. > > > Not trying to be overly fussy but this seems a bit ambiguous. What are > the > other options implied by "mostly"? Also you say "a table". Does this > mean > all extra variables are to be stored in one single table. I don''t think > this > matchs with what you wrote after but if you do want to store them all in > one > table do you really want to do this... > > http://trac.vaillant.ca/store.rb/wiki/DbModelVirtualFields > > > What I want to know, is what is the feasibility of allowing the user to >> do the following: create a new table in the database, > > > > a foreign key link >> to that table in the database (from the main table), and have rails >> modify the model of corresponding to the main table, to reflect the >> newly added foreign key (has_a relationship). >> >> > I think you can do all this easily except the has_a which would be more > involved. > > What is your example that you are working on? > > Peter-- Posted via http://www.ruby-forum.com/.
I don''t know how much control you have over the database, but since you are able to add tables, on the fly at that (scary), I am assuming you have full control. I also don''t know if you have legacy applications hitting this database, if you do how do they find the new tables? There are two solutions for this, add a table that handles choice types and add a foreign key relationship to choices, or just put the choice type name in the table. The second solution isn''t normalized, so I would suggest the first. So... Create a table choice_types with id and name columns. Then create another table choices with id, choice_type_id, value columns. In your ActiveRecord you will want to tell choice_type that it has many choices and tell the choice AR that it belongs to choice_types. Now you can interate over all of your choice_types to build a different selector, and then use choice_type.choices to retrieve all of the choices for that choice_type. This way you don''t have to create a table for each new choice type, you just have to add a row to the choice_type table. You can then add a bunch of choices to your choices table for that new choice_type. Good luck, I hope that helps. BTW... I am new to Ruby and Rails so please forgive me if I got some of the syntax incorrect. On 12/3/05, Eddie Stanley <eddiewould-wUU9E3n5/m4qAMOr+u8IRA@public.gmane.org> wrote:> ''ll be more specific about what I''m trying to achieve. The system will > be used by med students, who basically enter patient/consultation > details, come up a hypothesis (basically correlating symptoms and > patient variables to a diagnosis) and then using the database to test > the hypothesis, looking at the frequency with which the various > diagnoses occur, given the set of conditions (symptoms present, patient > variables). > > Depending on what the students decide to use as their hypothesis, they > may wish to examine things which have not been looked at in previous > years. Two which came up this year was ''Flu-Vaccine'' relating to when > the patient last had a flu vaccine. The options for this are stored in a > table with simply an id and a value - ''Never'', ''Unknown'', ''This year'', > ''Previous''. This is table is linked to from the Consultations table via > a foreign key. Likewise, there is a table ''SmokingStates'' with values > ''Ex'', ''Current'', ''Never'' & Unknown. > > In the future, perhaps the course administrator would like add a > variable ''Ethnicity''. This would need to be created as another table, > with simply two fields - id and value. There would be rows in the table > for the options for ethnicity, and we would need to add another field to > the Consultations table, which is a foreign key referencing to the value > for ethnicity. > > The SQL side of this isn''t going to be a problem - rails provides a way > to execute raw SQL, but perhaps more of a challenege is going to be > updating the rails models to reflect the new relationships. > > Surely someone has tackled this kind of problem before? > > Thanks very much for you ideas > Eddie > > > petermichaux wrote: > > On 12/2/05, Eddie Stanley <eddiewould-wUU9E3n5/m4qAMOr+u8IRA@public.gmane.org> wrote: > >> > >> It would be nice if the users (at least admin > >> users) could add extra paramaters (variables) to the database, as time > >> progresses. Mostly these will be stored in a table. > > > > > > Not trying to be overly fussy but this seems a bit ambiguous. What are > > the > > other options implied by "mostly"? Also you say "a table". Does this > > mean > > all extra variables are to be stored in one single table. I don''t think > > this > > matchs with what you wrote after but if you do want to store them all in > > one > > table do you really want to do this... > > > > http://trac.vaillant.ca/store.rb/wiki/DbModelVirtualFields > > > > > > What I want to know, is what is the feasibility of allowing the user to > >> do the following: create a new table in the database, > > > > > > > > a foreign key link > >> to that table in the database (from the main table), and have rails > >> modify the model of corresponding to the main table, to reflect the > >> newly added foreign key (has_a relationship). > >> > >> > > I think you can do all this easily except the has_a which would be more > > involved. > > > > What is your example that you are working on? > > > > Peter > > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Hi, I have full control over the database, and if my app is successful, it will replace the existing legacy application (written in PHP). I''d basically come to the same conclusion as you had - what I had in mind: A table called ''Tables'' or ''Research_Variables''. It would have at the least the following fields: ''id'' (PK), ''table_name'', ''foreign_key_name''. So basically whenever a new table is created, it will be added to this table. Perhaps I won''t be able to take full advantage of rails'' ActiveRecord if I do this, and might have to most of my querying through SQL nastyness. Your thoughts? Eddie carl.fyffe wrote:> I don''t know how much control you have over the database, but since > you are able to add tables, on the fly at that (scary), I am assuming > you have full control. I also don''t know if you have legacy > applications hitting this database, if you do how do they find the new > tables? > > There are two solutions for this, add a table that handles choice > types and add a foreign key relationship to choices, or just put the > choice type name in the table. The second solution isn''t normalized, > so I would suggest the first. > > So... Create a table choice_types with id and name columns. Then > create another table choices with id, choice_type_id, value columns. > > In your ActiveRecord you will want to tell choice_type that it has > many choices and tell the choice AR that it belongs to choice_types. > > Now you can interate over all of your choice_types to build a > different selector, and then use choice_type.choices to retrieve all > of the choices for that choice_type. > > This way you don''t have to create a table for each new choice type, > you just have to add a row to the choice_type table. You can then add > a bunch of choices to your choices table for that new choice_type. > > Good luck, I hope that helps. > > BTW... I am new to Ruby and Rails so please forgive me if I got some > of the syntax incorrect.-- Posted via http://www.ruby-forum.com/.
Hi Carl, I''ve just re-read your suggestion, and I understood it properly this time. Yes! It''s definately better, mostly because it will mean I don''t need to create a new table every time the user wants to add new a new ResearchVariable. I will give it some thought, the only thing that worries me about it is that it will be tricky to know if a ResearchVariable already exists - I guess a SELECT DISTINCT could help with that. Regards Eddie Stanley eddiewould wrote:> Hi, > > I have full control over the database, and if my app is successful, it > will replace the existing legacy application (written in PHP). > > I''d basically come to the same conclusion as you had - what I had in > mind: > > A table called ''Tables'' or ''Research_Variables''. > It would have at the least the following fields: > > ''id'' (PK), ''table_name'', ''foreign_key_name''. > > So basically whenever a new table is created, it will be added to this > table. Perhaps I won''t be able to take full advantage of rails'' > ActiveRecord if I do this, and might have to most of my querying through > SQL nastyness. > > Your thoughts? > Eddie > > carl.fyffe wrote: >> I don''t know how much control you have over the database, but since >> you are able to add tables, on the fly at that (scary), I am assuming >> you have full control. I also don''t know if you have legacy >> applications hitting this database, if you do how do they find the new >> tables? >> >> There are two solutions for this, add a table that handles choice >> types and add a foreign key relationship to choices, or just put the >> choice type name in the table. The second solution isn''t normalized, >> so I would suggest the first. >> >> So... Create a table choice_types with id and name columns. Then >> create another table choices with id, choice_type_id, value columns. >> >> In your ActiveRecord you will want to tell choice_type that it has >> many choices and tell the choice AR that it belongs to choice_types. >> >> Now you can interate over all of your choice_types to build a >> different selector, and then use choice_type.choices to retrieve all >> of the choices for that choice_type. >> >> This way you don''t have to create a table for each new choice type, >> you just have to add a row to the choice_type table. You can then add >> a bunch of choices to your choices table for that new choice_type. >> >> Good luck, I hope that helps. >> >> BTW... I am new to Ruby and Rails so please forgive me if I got some >> of the syntax incorrect.-- Posted via http://www.ruby-forum.com/.
a) Add a text column to the table called extended b) add serialize :extended, Hash c) Overwrite method missing so that unknown column style message will be forwarded to the hash On 12/2/05, Eddie Stanley <eddiewould-wUU9E3n5/m4qAMOr+u8IRA@public.gmane.org> wrote:> Hi, I want to write a rails application which is being used for querying > a research database. It would be nice if the users (at least admin > users) could add extra paramaters (variables) to the database, as time > progresses. Mostly these will be stored in a table. > > What I want to know, is what is the feasibility of allowing the user to > do the following: create a new table in the database, a foreign key link > to that table in the database (from the main table), and have rails > modify the model of corresponding to the main table, to reflect the > newly added foreign key (has_a relationship). > > Your thoughts / opinions / ideas greately appreciated! > > Eddie > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Tobi http://jadedpixel.com - modern e-commerce software http://typo.leetsoft.com - Open source weblog engine http://blog.leetsoft.com - Technical weblog