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
'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
On 12/2/05, Eddie S. [email protected] 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
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.
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 S. [email protected] wrote:
–
Tobi
http://jadedpixel.com - modern e-commerce software
http://typo.leetsoft.com - Open source weblog engine
http://blog.leetsoft.com - Technical weblog
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.
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 S.
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.