Best way to handle multiple tables to replace one big table in Rails? (e.g. 'Books1', 'Books2', etc.
- by mikep
Hello,
I've decided to use multiple tables for an entity (e.g. Books1, Books2, Books3, etc.), instead of just one main table which could end up having a lot of rows (e.g. just Books). I'm doing this to try and to avoid a potential future performance drop that could come with having too many rows in one table.
With that, I'm looking for a good way to handle this in Rails, mainly by trying to avoid loading a bunch of unused associations.
(I know that I could use a partition for this, but, for now, I've decided to go the 'multiple tables' route.)
Each user has their books placed into a specific table. The actual book table is chosen when the user is created, and all of their books go into the same table. I'm going to split the adds across the tables. The goal is to try and keep each table pretty much even -- but that's a different issue.
One thing I don't particularly want to have is a bunch of unused associations in the User class. Right now, it looks like I'd have to do the following:
class User < ActiveRecord::Base
has_many :books1, :books2, :books3, :books4, :books5
end
class Books1 < ActiveRecord::Base
belongs_to :user
end
class Books2 < ActiveRecord::Base
belongs_to :user
end
class Books3 < ActiveRecord::Base
belongs_to :user
end
I'm assuming that the main performance hit would come in terms of memory and possibly some method call overhead for each User object, since it has to load all of those associations, which in turn creates all of those nice, dynamic model accessor methods like User.find_by_.
But for each specific user, only one of the book tables would be usable/applicable, since all of a user's books are stored in the same table. So, only one of the associations would be in use at any time and any other has_many :bookX association that was loaded would be a waste.
For example, with a user.id of 2, I'd only need books3.find_by_author('Author'), but the way I'm thinking of setting this up, I'd still have access to Books1..n.
I don't really know Ruby/Rails does internally with all of those has_many associations though, so maybe it's not so bad. But right now I'm thinking that it's really wasteful, and that there may just be a better, more efficient way of doing this.
So, a few questions:
1) Is there's some sort of special Ruby/Rails methodology that could be applied to this 'multiple tables to represent one entity' scheme? Are there any 'best practices' for this?
2) Is it really bad to have so many unused has_many associations for each object? Is there a better way to do this?
3) Does anyone have any advice on how to abstract the fact that there's multiple book tables behind a single books model/class? For example, so I can call books.find_by_author('Author') instead of books3.find_by_author('Author').
Thank you!
