Hi everyone,
I’m a signal processing / math analysis programmer just getting
interested in programming for the web. RoR seems to be the system to
learn. I have read through the O’Reilly cookbook and pragmatic Depot
applications, and am impressed by how much can be accomplished so
quickly. I’m also impressed with the level of support that this
mailing list seems to offer to newbies such as myself!
My problem relates to database organization. I am designing a site
that will support many users, each of whom will create many lines of
data in the base tables of the database. The number of lines per user
will increase linearly with time. (no pun intended.)
So my question is, should I have just a single set of base tables that
will contain the data for all the users, or should I be generating a
separate set of base tables for each user? Performance-wise I would
expect better results if each user had their own set of tables, but I
don’t know
- if there are any restrictions on the number of tables a database can
handle,
- if it is convenient to create and destroy tables from within Rails,
- if my performance assumptions are correct, and
- if this whole worry is stupid and I should just deal with scaling
issues as they happen (and be happy that so many people are using my
app!)
Thanks for any help you can provide.
Ben
Hi everyone,
I’m a signal processing / math analysis programmer just getting
interested in programming for the web. RoR seems to be the system to
learn. I have read through the O’Reilly cookbook and pragmatic Depot
applications, and am impressed by how much can be accomplished so
quickly. I’m also impressed with the level of support that this
mailing list seems to offer to newbies such as myself!
My problem relates to database organization. I am designing a site
that will support many users, each of whom will create many lines of
data in the base tables of the database. The number of lines per user
will increase linearly with time. (no pun intended.)
So my question is, should I have just a single set of base tables that
will contain the data for all the users, or should I be generating a
separate set of base tables for each user? Performance-wise I would
expect better results if each user had their own set of tables, but I
don’t know
- if there are any restrictions on the number of tables a database can
handle,
- if it is convenient to create and destroy tables from within Rails,
- if my performance assumptions are correct, and
- if this whole worry is stupid and I should just deal with scaling
issues as they happen (and be happy that so many people are using my
app!)
Thanks for any help you can provide.
Ben
- if there are any restrictions on the number of tables a database can
handle,
Dont think so, its more total size of database.
- if it is convenient to create and destroy tables from within Rails,
Yes. Migrations can do this and im sure you can call migration code
outside of migration files eg. create_table :user
- if my performance assumptions are correct, and
- if this whole worry is stupid and I should just deal with scaling
issues as they happen (and be happy that so many people are using my
app!)
It would be easy to upgrade your code from one user table to many quite
easily you just need to work out how you are going to prefix the tables,
username or user_id.
You would use set_table_name in the model to set the table name
dynamically.
But it depends if you are going to keep the User class in the session
because keeping class with dynamic prefixes in the session is hard
because you cant get the prefix to the model before it needs creating.
You will be very very happy after you see this:
http://media.rubyonrails.org/video/migrations.mov
If you’re used to writing SQL by hand, then it really kind of has to
be seen to be believed. 
–
Giles B.
http://www.gilesgoatboy.org
I think that your ‘many table’ approach would work, but my knee-jerk
reaction is that it’s not a good idea.
RDBMSs are explicitly designed to handle very large tables, and the
index
types available in RDBMSs make lookups in large tables very fast
(especially
on unique keys.)
It’s also contrary to the way Rails is designed- Rails assumes that each
model corresponds to a table, and that each row in the table corresponds
to
an instance of the model. I’m sure you could work around it, but it’s
more
work.
Finally, your comment about scaling fits with my prejudice- “deal with
scaling issues as they happen.” That is, don’t do things that are
obviously
bad or stupid, but don’t spend too much time on up front analysis- you
don’t
really know what the problems will be until you test it, and the
bottlenecks
usually aren’t where you’d expect.
My general advice would be to go with a big table- if you go with lots
of
tables, you’re swimming against the stream both with regard to how
RDBMSs
are designed, and with regard to how ORM tools like Rails are designed.
You
can probably make it work, but it’s better to choose your battles- save
your
strength for the innovations that are important to your app, instead of
spending a lot of time and effort on redesigning object-relational
mapping.
On Apr 20, 2006, at 9:19 AM, Ben Nevile wrote:
So my question is, should I have just a single set of base tables that
will contain the data for all the users, or should I be generating a
separate set of base tables for each user? Performance-wise I would
expect better results if each user had their own set of tables, but I
don’t know
- if there are any restrictions on the number of tables a database
can handle,
There are always limits, but they’re very, very high.
- if it is convenient to create and destroy tables from within Rails,
As you have access to the raw DB connection, it’s possible, but I’d
hesitate to say convenient.
- if my performance assumptions are correct, and
I think they’re not. 
DBs are designed to handle the scenario
you’re describing efficiently.
- if this whole worry is stupid and I should just deal with scaling
issues as they happen (and be happy that so many people are using my
app!)
Bingo.
–
– Tom M.