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