Dynamic tables creation and handling


#1

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


#2

Don’t.


#3

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…


#4

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” removed_email_address@domain.invalid
To: removed_email_address@domain.invalid
Sent: Tuesday, November 29, 2005 1:15 AM
Subject: [Rails] Re: Dynamic tables creation and handling


#5

How about dynamically creating a View named Inventories pointing to the
current Inventory table?


#6

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 N.” removed_email_address@domain.invalid
To: removed_email_address@domain.invalid
Sent: Tuesday, November 29, 2005 8:34 AM
Subject: [Rails] Dynamic tables creation and handling


#7

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


#8

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. :slight_smile:

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


#9

Allen F. 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.


#10

dba wrote:

Thomas Kwan (eTechfocus) wrote:

Hi Christer,

He was referring to database views, not the MVC View component. :slight_smile:

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


#11

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 :wink:

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.