Forum: Ruby on Rails a table for every user?

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.
91ac2635d306867b3fb429d28c53ef3b?d=identicon&s=25 Ben Nevile (Guest)
on 2006-04-20 17:57
(Received via mailing list)
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
1. if there are any restrictions on the number of tables a database can
handle,
2. if it is convenient to create and destroy tables from within Rails,
3. if my performance assumptions are correct, and
4. 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
Ec79cd3f214f955ba462881333914815?d=identicon&s=25 Ben Nevile (Guest)
on 2006-04-20 18:22
(Received via mailing list)
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
1. if there are any restrictions on the number of tables a database can
handle,
2. if it is convenient to create and destroy tables from within Rails,
3. if my performance assumptions are correct, and
4. 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
3d333b0012928f3dd5a6861cb09ad683?d=identicon&s=25 Kris (Guest)
on 2006-04-20 18:25
1. if there are any restrictions on the number of tables a database can
handle,

Dont think so, its more total size of database.

2. 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

3. if my performance assumptions are correct, and
4. 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.
3ec705c5dd3480c6268b72c5617e8dae?d=identicon&s=25 Michael Smedberg (Guest)
on 2006-04-20 18:40
(Received via mailing list)
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.
Ce8b03e5750097942c58e12b46724312?d=identicon&s=25 Giles Bowkett (Guest)
on 2006-04-20 18:56
(Received via mailing list)
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 Bowkett
http://www.gilesgoatboy.org
59de94a56fd2c198f33d9515d1c05961?d=identicon&s=25 Tom Mornini (Guest)
on 2006-04-20 19:39
(Received via mailing list)
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
> 1. if there are any restrictions on the number of tables a database
> can handle,

There are always limits, but they're very, very high.

> 2. 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.

> 3. if my performance assumptions are correct, and

I think they're not. :-) DBs are designed to handle the scenario
you're describing efficiently.

> 4. 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 Mornini
This topic is locked and can not be replied to.