Not sure if this has been addressed before (didn''t see anything in archives or irc), but I addressed one solution to lookup tables I threw together earlier today in my blog: http://life.lukewarmtapioca.com/articles/2005/04/25/lookup-tables-in-rails Not sure if it''s any good or not :) but it seems to work nicely. Dropdowns are populated dynamically from the database, etc, etc. Just need to add doing SELECTED properly when editing. Britt
On 4/26/05, Britt Selvitelle <anotherbritt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Not sure if this has been addressed before (didn''t see anything in > archives or irc), but I addressed one solution to lookup tables I > threw together earlier today in my blog: > > http://life.lukewarmtapioca.com/articles/2005/04/25/lookup-tables-in-rails > > Not sure if it''s any good or not :) but it seems to work nicely. > Dropdowns are populated dynamically from the database, etc, etc. Just > need to add doing SELECTED properly when editing.I always populate these lookup values (often called ''type'' columns) using Description classes rather than strings with special meanings. For example if I''d have a Gender model, and Person would belong_to :gender. I''m with gavin king here, you should have as many (or more) classes as you have tables.> Britt > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Cheers Koz
On 4/25/05, Michael Koziarski <koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> For example if I''d have a Gender model, and Person would belong_to > :gender. I''m with gavin king here, you should have as many (or more) > classes as you have tables.I''ve used this type of scheme before. I dislike it for large databases because you end up with a billion lookup tables. Having one is breaking 3rd normal form a bit, but in general I find it a bit cleaner. Britt
On 4/26/05, Britt Selvitelle <anotherbritt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On 4/25/05, Michael Koziarski <koziarski-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > For example if I''d have a Gender model, and Person would belong_to > > :gender. I''m with gavin king here, you should have as many (or more) > > classes as you have tables. > > I''ve used this type of scheme before. I dislike it for large databases > because you end up with a billion lookup tables. Having one is > breaking 3rd normal form a bit, but in general I find it a bit > cleaner.It''s probably just a matter of preference, but I''m in favour of this for any non-trivial databases (>10 tables), and I''d really struggle without it on large (>100) databases. I prefer the referential integrity checking built into the DBMS and it *really* helps the maintainers when they can see that "AddressType" is a foreign key rather than some arbitrary string. -- Cheers Koz
> It''s probably just a matter of preference, but I''m in favour of this > for any non-trivial databases (>10 tables), and I''d really struggle > without it on large (>100) databases. I prefer the referential > integrity checking built into the DBMS and it *really* helps the > maintainers when they can see that "AddressType" is a foreign key > rather than some arbitrary string.As someone who does maintain a database built with the single lookup table idea, I would fully recommend going for full 3NF. It''s not just a fancy idea dreamt up by people to sell books, it''s a very useful system when it comes to maintenance. I look at the database, and it''s just a mess. You can''t code in referential integrity for that pattern, you just can''t see what links to where. Sure, extra tables may mean more overhead, but the reduced maintenance time is worth it. -- Phillip Hutchings http://www.sitharus.com/ sitharus-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org / sitharus-QrR4M9swfipWk0Htik3J/w@public.gmane.org
I am not sure if this subject was raised before, but I could not find a specific answer in the documentation. I am developing a rails application in which there are "projects" which share the same models. A user can only work on a single project at a time. The project on which the user is working, is kept in the session. I would like each project to have an independent set of tables. So if there is a model "Personality", I would like rails to access the tables "project1_personalities" and "project2_personalities" depending on the project the user is working on. Is this dynamic choice of tables possible? The reason I need this is because the data in each project will then be processed outside the application. Thanks, Pedro Santos
Pedro Santos wrote:>I am not sure if this subject was raised before, but I could not find a >specific answer in the documentation. > >I am developing a rails application in which there are "projects" which >share the same models. A user can only work on a single project at a time. >The project on which the user is working, is kept in the session. I would >like each project to have an independent set of tables. > >So if there is a model "Personality", I would like rails to access the >tables "project1_personalities" and "project2_personalities" depending on >the project the user is working on. Is this dynamic choice of tables >possible? > >The reason I need this is because the data in each project will then be >processed outside the application. > >Thanks, > >Pedro Santos > >_______________________________________________ >Rails mailing list >Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >http://lists.rubyonrails.org/mailman/listinfo/rails > > >set_table_name takes a block so you could do the following: class MyClass < ActiveRecord::Base set_table_name nil, { my_table_name } def self.my_table_name < something that is a function of project > end end
Thanks for your answer. But in the production environment, would not the Class only be loaded once (say, pointing to table1), and requests regarding project2 be subsequently pointed to the wrong table? Pedro on 05/08/08 19:25, Steve Downey at sldowney-TVLZxgkOlNX2fBVCVOL8/A@public.gmane.org wrote:> Pedro Santos wrote: > >> I am not sure if this subject was raised before, but I could not find a >> specific answer in the documentation. >> >> I am developing a rails application in which there are "projects" which >> share the same models. A user can only work on a single project at a time. >> The project on which the user is working, is kept in the session. I would >> like each project to have an independent set of tables. >> >> So if there is a model "Personality", I would like rails to access the >> tables "project1_personalities" and "project2_personalities" depending on >> the project the user is working on. Is this dynamic choice of tables >> possible? >> >> The reason I need this is because the data in each project will then be >> processed outside the application. >> >> Thanks, >> >> Pedro Santos >> >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> >> > set_table_name takes a block so you could do the following: > > class MyClass < ActiveRecord::Base > set_table_name nil, { my_table_name } > > def self.my_table_name > < something that is a function of project > > end > > end > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Pedro Santos wrote:>Thanks for your answer. > >But in the production environment, would not the Class only be loaded once >(say, pointing to table1), and requests regarding project2 be subsequently >pointed to the wrong table? > > > > > >>set_table_name takes a block so you could do the following: >> >>class MyClass < ActiveRecord::Base >>set_table_name nil, { my_table_name } >> >>def self.my_table_name >> < something that is a function of project > >>end >> >>end >> >>It''s a problem, but probably not insurmountable. The code in <something ... project> would look something like: "project#{project_no}_tablename" The challenge is to write a project_no() method that returns the project number.
A lot of people will view your database design as BAD! after you start using this your going to find adding x amount of tables for every new project is going to be very cumbersome, annoying, and a large pain in the ass. have ANY amount of tables with the EXACT same structure is a BAD idea! (unless your doing some type of datamining and one of the tables is used for archiving that is rarely queried) you should consider adding a ''projects'' tables, and adding a project_id column to all tables to keep information seperate, and clean. On 8/9/05, Steve Downey <sldowney-TVLZxgkOlNX2fBVCVOL8/A@public.gmane.org> wrote:> Pedro Santos wrote: > > >Thanks for your answer. > > > >But in the production environment, would not the Class only be loaded once > >(say, pointing to table1), and requests regarding project2 be subsequently > >pointed to the wrong table? > > > > > > > > > > > >>set_table_name takes a block so you could do the following: > >> > >>class MyClass < ActiveRecord::Base > >>set_table_name nil, { my_table_name } > >> > >>def self.my_table_name > >> < something that is a function of project > > >>end > >> > >>end > >> > >> > > It''s a problem, but probably not insurmountable. The code in <something > ... project> would look something like: > > "project#{project_no}_tablename" > > The challenge is to write a project_no() method that returns the project > number. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Zachery Hostens <zacheryph-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
on 05/08/10 9:03 AM, Zachery Hostens at zacheryph-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote:> A lot of people will view your database design as BAD! >I agree with you, and I am still undecided about the best design. The adavantages of multiple tables is mainly: -Before the external processing of a project, a backup of the specific tables can be made. If some problem occur due to the the external processing, the table can be restored without affecting all the other projects. Note that the processing is complex, and involves random factors, so the probability of something bad or unexpected happening is relatively high. Pedro> after you start using this your going to find adding x amount of > tables for every new project is going to be very cumbersome, annoying, > and a large pain in the ass. > > have ANY amount of tables with the EXACT same structure is a BAD idea! > (unless your doing some type of datamining and one of the tables is > used for archiving that is rarely queried) > > you should consider adding a ''projects'' tables, and adding a > project_id column to all tables to keep information seperate, and > clean. > > > On 8/9/05, Steve Downey <sldowney-TVLZxgkOlNX2fBVCVOL8/A@public.gmane.org> wrote: >> Pedro Santos wrote: >> >>> Thanks for your answer. >>> >>> But in the production environment, would not the Class only be loaded once >>> (say, pointing to table1), and requests regarding project2 be subsequently >>> pointed to the wrong table? >>> >>> >>> >>> >>> >>>> set_table_name takes a block so you could do the following: >>>> >>>> class MyClass < ActiveRecord::Base >>>> set_table_name nil, { my_table_name } >>>> >>>> def self.my_table_name >>>> < something that is a function of project > >>>> end >>>> >>>> end >>>> >>>> >> >> It''s a problem, but probably not insurmountable. The code in <something >> ... project> would look something like: >> >> "project#{project_no}_tablename" >> >> The challenge is to write a project_no() method that returns the project >> number. >> _______________________________________________ >> Rails mailing list >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >
Why not have a single set of project tables to contain all project info, then for external processing copy the project information related to the project being externally altered a set of staging tables, do your processing and if that is successful update the original source project tables with any updates, then clean up your staging tables, or keep the records and mark them as processed. OR just have your external processing key on the specific projects being altered and use transactions ? Lee On 8/10/05, Pedro Santos <pedro.santos-s6mEjpzMaPUVhHzd4jOs4w@public.gmane.org> wrote:> on 05/08/10 9:03 AM, Zachery Hostens at zacheryph-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote: > > > A lot of people will view your database design as BAD! > > > > I agree with you, and I am still undecided about the best design. The > adavantages of multiple tables is mainly: > > -Before the external processing of a project, a backup of the specific > tables can be made. If some problem occur due to the the external > processing, the table can be restored without affecting all the other > projects. > > Note that the processing is complex, and involves random factors, so the > probability of something bad or unexpected happening is relatively high. > > Pedro > > > > after you start using this your going to find adding x amount of > > tables for every new project is going to be very cumbersome, annoying, > > and a large pain in the ass. > > > > have ANY amount of tables with the EXACT same structure is a BAD idea! > > (unless your doing some type of datamining and one of the tables is > > used for archiving that is rarely queried) > > > > you should consider adding a ''projects'' tables, and adding a > > project_id column to all tables to keep information seperate, and > > clean. > > > > > > On 8/9/05, Steve Downey <sldowney-TVLZxgkOlNX2fBVCVOL8/A@public.gmane.org> wrote: > >> Pedro Santos wrote: > >> > >>> Thanks for your answer. > >>> > >>> But in the production environment, would not the Class only be loaded once > >>> (say, pointing to table1), and requests regarding project2 be subsequently > >>> pointed to the wrong table? > >>> > >>> > >>> > >>> > >>> > >>>> set_table_name takes a block so you could do the following: > >>>> > >>>> class MyClass < ActiveRecord::Base > >>>> set_table_name nil, { my_table_name } > >>>> > >>>> def self.my_table_name > >>>> < something that is a function of project > > >>>> end > >>>> > >>>> end > >>>> > >>>> > >> > >> It''s a problem, but probably not insurmountable. The code in <something > >> ... project> would look something like: > >> > >> "project#{project_no}_tablename" > >> > >> The challenge is to write a project_no() method that returns the project > >> number. > >> _______________________________________________ > >> Rails mailing list > >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > >> http://lists.rubyonrails.org/mailman/listinfo/rails > >> > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
I think your trying to make your life THAT much more complicated just for the sake of "what if" and "in case" situations. if something goes wrong in the database everyone is gonna notice. if this is truly a production environment, then the data should be on a RAID''ed disk setup and backed up daily, not to mention database maintenance ran weekly or more to ensure its stability/reliability. having a database with 500 tables for the oh so many projects is gonna have you kicking your own ass in the future going "wtf was i thinking", not to mention the database kicking ass for having to keep track of and index that many damn tables. and when something does go wrong you have that many more tables to repair. if projects truly are to remain seperate then put them in seperate databases. but this does not sound at all like what you want to do. (note: cross-database referencing is BAD!) On 8/10/05, Lee Gonzales <lee.gonzales-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Why not have a single set of project tables to contain all project > info, then for external processing copy the project information > related to the project being externally altered a set of staging > tables, do your processing and if that is successful update the > original source project tables with any updates, then clean up your > staging tables, or keep the records and mark them as processed. > > OR just have your external processing key on the specific projects > being altered and use transactions ? > > Lee > > > On 8/10/05, Pedro Santos <pedro.santos-s6mEjpzMaPUVhHzd4jOs4w@public.gmane.org> wrote: > > on 05/08/10 9:03 AM, Zachery Hostens at zacheryph-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org wrote: > > > > > A lot of people will view your database design as BAD! > > > > > > > I agree with you, and I am still undecided about the best design. The > > adavantages of multiple tables is mainly: > > > > -Before the external processing of a project, a backup of the specific > > tables can be made. If some problem occur due to the the external > > processing, the table can be restored without affecting all the other > > projects. > > > > Note that the processing is complex, and involves random factors, so the > > probability of something bad or unexpected happening is relatively high. > > > > Pedro > > > > > > > after you start using this your going to find adding x amount of > > > tables for every new project is going to be very cumbersome, annoying, > > > and a large pain in the ass. > > > > > > have ANY amount of tables with the EXACT same structure is a BAD idea! > > > (unless your doing some type of datamining and one of the tables is > > > used for archiving that is rarely queried) > > > > > > you should consider adding a ''projects'' tables, and adding a > > > project_id column to all tables to keep information seperate, and > > > clean. > > > > > > > > > On 8/9/05, Steve Downey <sldowney-TVLZxgkOlNX2fBVCVOL8/A@public.gmane.org> wrote: > > >> Pedro Santos wrote: > > >> > > >>> Thanks for your answer. > > >>> > > >>> But in the production environment, would not the Class only be loaded once > > >>> (say, pointing to table1), and requests regarding project2 be subsequently > > >>> pointed to the wrong table? > > >>> > > >>> > > >>> > > >>> > > >>> > > >>>> set_table_name takes a block so you could do the following: > > >>>> > > >>>> class MyClass < ActiveRecord::Base > > >>>> set_table_name nil, { my_table_name } > > >>>> > > >>>> def self.my_table_name > > >>>> < something that is a function of project > > > >>>> end > > >>>> > > >>>> end > > >>>> > > >>>> > > >> > > >> It''s a problem, but probably not insurmountable. The code in <something > > >> ... project> would look something like: > > >> > > >> "project#{project_no}_tablename" > > >> > > >> The challenge is to write a project_no() method that returns the project > > >> number. > > >> _______________________________________________ > > >> Rails mailing list > > >> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > >> http://lists.rubyonrails.org/mailman/listinfo/rails > > >> > > > > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Zachery Hostens <zacheryph-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>