I want to create a RAILS application to handle inventories from multiple companies. Each inventory is potentially huge (> 500K entries) so I want to store each company''s inventory into a separate SQL table. Imagine that, I may have inventory_001 inventory_002 inventory_003 ... How should handle I the table creation? And how can I create a model class that can be used for multiple tables. Does anyone know of any sample application that does this? thanks -- Posted via http://www.ruby-forum.com/.
Don''t. -- Posted via http://www.ruby-forum.com/.
Thomas Kwan <thomask@...> writes:> > I want to create a RAILS application to handle inventories from > multiple companies. Each inventory is potentially huge > (> 500K entries) so I want to store each company''s inventory > into a separate SQL table. Imagine that, I may haveIf the only reason to use multiple tables is that you worry about performance, you should at least do a load test with a unique table. Premature optimization and all that...
Thomas Kwan (eTechfocus)
2005-Nov-29 15:42 UTC
Re: Re: Dynamic tables creation and handling
Hi Alex, Performance is one plus. It is also maintainbility. Smaller tables mean you can do index-fixing on one table without affecting others. It is easier to backup and restore via phpAdmin. If you want to change web hosting company, smaller table will give you higher flexibility. You can also distribute your partitioned data among multiple web hosts which give you higher reliability. I am currently hosting a site which has more than 20M entries (30G diskspace). It still works, and performance is reasonable. But managing such huge table is a pain. thanks thomas ----- Original Message ----- From: "Alex Drahon" <adrahon-97jfqw80gc6171pxa8y+qA@public.gmane.org> To: <rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> Sent: Tuesday, November 29, 2005 1:15 AM Subject: [Rails] Re: Dynamic tables creation and handling> Thomas Kwan <thomask@...> writes: > >> >> I want to create a RAILS application to handle inventories from >> multiple companies. Each inventory is potentially huge >> (> 500K entries) so I want to store each company''s inventory >> into a separate SQL table. Imagine that, I may have > > If the only reason to use multiple tables is that you worry about > performance, > you should at least do a load test with a unique table. Premature > optimization > and all that... > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
How about dynamically creating a View named Inventories pointing to the current Inventory table? -- Posted via http://www.ruby-forum.com/.
Hi Christer, Thanks for the response. I am still new to rails. So please forgive my ignorance. In rails, the controller ties the model and the view together. So when you said multiple views, are you referring 1 controller for multiple views? If that is the case, each method in the controller probably needs to accept an additional parameter that will be mapped into a table name, right? thomas ----- Original Message ----- From: "Christer Nilsson" <janchrister.nilsson-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> To: <rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org> Sent: Tuesday, November 29, 2005 8:34 AM Subject: [Rails] Dynamic tables creation and handling> How about dynamically creating a View named Inventories pointing to the > current Inventory table? > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails
If you want to do this, you can override ActiveRecord''s find*() queries
for the table and implement some dynamic table names. Something like...
def find_by_id_and_inventory_id(id, inventory_id=001)
# assuming inventory_id is never user-tainted data
find_by_sql([
"select *
from inventory_{#inventory_id}
where id=?",id])
end
Of course, you would have to override the save() method in similar fashion.
As with anything in Rails, if you don''t abide by conventions, you have
to do more coding. But its still more a joy than other frameworks!
An alternative approach I thought of was to use stored procedures to
access these tables for you. The procedures would do similar decoding of
the table name.
> Performance is one plus. It is also maintainbility.
> Smaller tables mean you can do index-fixing on
> one table without affecting others.
FYI, if you are using Postgres, look into partial indexes. They index
only a portion of the table that require special access without indexing
the full table.
> It is easier to> backup and restore via phpAdmin. If you want to
> change web hosting company, smaller table will
> give you higher flexibility. You can also distribute
> your partitioned data among multiple web hosts
> which give you higher reliability.
But this makes more a chore for other maintenance and manual queries,
which you will do more often than changing web hosting.
Also, hope you don''t forget to do *every* inventory_* table! We know it
will happen someday when you least expect it.
Consider if the handling iventory_001 through 999 (maybe someday!) will
slow down your administration and cause potential errors.
Better reliability can be found with a good replication scheme to
another host. That way, all your data will be available if something
happens to your master database.
Those are my thoughts. My opinion from experience is to use one table.
Whatever path you choose, good luck!
Allen
Thomas Kwan (eTechfocus) wrote:> Hi Christer, > > Thanks for the response. I am still new to rails. So please > forgive my ignorance. > In rails, the controller ties the model and the view together. > So when you said multiple views, are you referring 1 controller > for multiple views? If that is the case, each method in the > controller probably needs to accept an additional parameter > that will be mapped into a table name, right?He was referring to database views, not the MVC View component. :-) CREATE VIEW name [ ( column_name [, ...] ) ] AS query This can be used as an alias for another table or subset of table rows, or a join. His idea would be to recreate the view on the fly to point to a particular table. I don''t think this is what you want, assuming you want each inventory_* table accessable at the same time Allen
Allen Fair wrote:> CREATE VIEW name [ ( column_name [, ...] ) ] AS query > > This can be used as an alias for another table or subset of table rows, > or a join. > > His idea would be to recreate the view on the fly to point to a > particular table. I don''t think this is what you want, assuming you want > each inventory_* table accessable at the same timeyou can do it the other way around: create the tables like this inventory_001 inventory_002 inventory_003 and then make a view selecting them together with an union. create or replace view inventory as select * from inventory_001 union select * from inventory_002 and so on so you can have a single inventory model that binds to the view.
dba wrote:> Thomas Kwan (eTechfocus) wrote: >> Hi Christer,> He was referring to database views, not the MVC View component. :-) > > CREATE VIEW name [ ( column_name [, ...] ) ] AS query > > This can be used as an alias for another table or subset of table rows, > or a join. > > His idea would be to recreate the view on the fly to point to a > particular table. I don''t think this is what you want, assuming you want > each inventory_* table accessable at the same time > > AllenThanks Allen, Acutally, it is not an requirement to access those tables at the same time. thomas -- Posted via http://www.ruby-forum.com/.
True, but a union may give performance, as each table would have to be accessed in turn, and its results concatenated. That doesn''t scale as the number of tables increases. However, I think Thomas has the idea ;-) Allen Simon.Santoro wrote:> you can do it the other way around: create the tables like this > inventory_001 > inventory_002 > inventory_003 > and then make a view selecting them together with an union. > create or replace view inventory as > select * from inventory_001 > union > select * from inventory_002 > and so on > so you can have a single inventory model that binds to the view.-- Posted via http://www.ruby-forum.com/.