# A table for every user? **URL:** \ **Category:** Rails\ **Created:** [April 20, 2006, 5:57pm UTC](https://www.ruby-forum.com/t/a-table-for-every-user/58337 "2006-04-20T17:57:48Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Ben\_Nevile](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/48db29/32.png) [@Ben\_Nevile](https://www.ruby-forum.com/u/Ben_Nevile)\ **Post date:** [April 20, 2006, 5:57pm UTC](https://www.ruby-forum.com/t/a-table-for-every-user/58337/1 "2006-04-20T17:57:48Z")
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 ---
**Author:** ![Ben\_Nevile](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/48db29/32.png) [@Ben\_Nevile](https://www.ruby-forum.com/u/Ben_Nevile)\ **Post date:** [April 20, 2006, 6:22pm UTC](https://www.ruby-forum.com/t/a-table-for-every-user/58337/2 "2006-04-20T18:22:18Z")
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 ---
**Author:** ![Ben\_Nevile](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/48db29/32.png) [@Ben\_Nevile](https://www.ruby-forum.com/u/Ben_Nevile)\ **Post date:** [April 20, 2006, 6:25pm UTC](https://www.ruby-forum.com/t/a-table-for-every-user/58337/3 "2006-04-20T18:25:05Z")
1. if there are any restrictions on the number of tables a database can handle, Dont think so, its more total size of database. 1. 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 1. if my performance assumptions are correct, and 2. 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. ---
**Author:** ![Ben\_Nevile](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/48db29/32.png) [@Ben\_Nevile](https://www.ruby-forum.com/u/Ben_Nevile)\ **Post date:** [April 20, 2006, 6:56pm UTC](https://www.ruby-forum.com/t/a-table-for-every-user/58337/4 "2006-04-20T18:56:20Z")
You will be very very happy after you see this: [http://media.rubyonrails.org/video/migrations.mov](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](http://www.gilesgoatboy.org) ---
**Author:** ![Ben\_Nevile](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/48db29/32.png) [@Ben\_Nevile](https://www.ruby-forum.com/u/Ben_Nevile)\ **Post date:** [April 20, 2006, 6:40pm UTC](https://www.ruby-forum.com/t/a-table-for-every-user/58337/5 "2006-04-20T18:40:42Z")
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. ---
**Author:** ![Ben\_Nevile](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/48db29/32.png) [@Ben\_Nevile](https://www.ruby-forum.com/u/Ben_Nevile)\ **Post date:** [April 20, 2006, 7:39pm UTC](https://www.ruby-forum.com/t/a-table-for-every-user/58337/6 "2006-04-20T19:39:31Z")
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. > 1. 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. > 1. if my performance assumptions are correct, and I think they’re not. 🙂 DBs are designed to handle the scenario you’re describing efficiently. > 1. 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.