Forum: Ruby on Rails Dynamic tables creation and handling

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
D53151d3fb2de557729740292239d270?d=identicon&s=25 Thomas Kwan (thomask)
on 2005-11-29 05:09
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
Af95bdaf87958c40150b813e94381bfd?d=identicon&s=25 Christer Nilsson (christer)
on 2005-11-29 09:11
Don't.
745e0beb75bab339cb734d67b9ac724b?d=identicon&s=25 adrahon (Guest)
on 2005-11-29 16:17
(Received via mailing list)
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...
D53151d3fb2de557729740292239d270?d=identicon&s=25 Thomas Kwan (thomask)
on 2005-11-29 16:45
(Received via mailing list)
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@fastmail.fm>
To: <rails@lists.rubyonrails.org>
Sent: Tuesday, November 29, 2005 1:15 AM
Subject: [Rails] Re: Dynamic tables creation and handling
Af95bdaf87958c40150b813e94381bfd?d=identicon&s=25 Christer Nilsson (christer)
on 2005-11-29 17:34
How about dynamically creating a View named Inventories pointing to the
current Inventory table?
D53151d3fb2de557729740292239d270?d=identicon&s=25 Thomas Kwan (thomask)
on 2005-11-29 18:18
(Received via mailing list)
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@gmail.com>
To: <rails@lists.rubyonrails.org>
Sent: Tuesday, November 29, 2005 8:34 AM
Subject: [Rails] Dynamic tables creation and handling
B9f28a2235697025ebfcebb81575a6b8?d=identicon&s=25 dba (Guest)
on 2005-11-29 18:27
(Received via mailing list)
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
B9f28a2235697025ebfcebb81575a6b8?d=identicon&s=25 dba (Guest)
on 2005-11-29 18:31
(Received via mailing list)
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
B6e90ad1e69d563f1f13cccffe137262?d=identicon&s=25 Simon.Santoro (Guest)
on 2005-11-29 19:19
(Received via mailing list)
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 time

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.
D53151d3fb2de557729740292239d270?d=identicon&s=25 Thomas Kwan (Guest)
on 2005-11-29 19:37
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
>
> Allen

Thanks Allen,

Acutally, it is not an requirement to access those tables at the same
time.

thomas
2fa87d4eaa7d6a499cf05527f8898428?d=identicon&s=25 Allen Fair (allen)
on 2005-11-29 21:01
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.
This topic is locked and can not be replied to.