How to structure model that is broken into database as 36 tables?

Question

I have over 1 billion domain name records which, instead of putting them all in a single table, I decided to break them up into 36 tables (same db structure for each table).

There is a table based on the first character of the domain name (ex tables: domains_a... domains_z).

How can I create a single Domain model in rails that automatically switches between these tables seamlessly based on the character specified?

Answer 1

Generally, this sort of table partitioning is handled at the database level. You should specify what database you are using, because that will be extremely relevant here.

For instance, PostgreSQL has basic table partition support. You would point the Rails model at the master table, and the partitioning would be transparent to the Ruby layer.

Answer 2

You can't: you have to write your own logic to deal with that. Rails would need to know your business logic and analyze the SQL query to find out which table to pick and can't do that by default, you need to write that code by yourself.

However there is a trick that will make it extremely easier for you. What about handling this on the database level? I've checked and all major databases support updatable views.

So, create a new view, name it domains and make sure it creates a union of all your domain tables (from a to z), then createa model:

class Domain
  self.table_name = "your_view_name"
end

This would do the trick for read side. Now based on the database you are using, you might be able to solve also the write problem in this way (with triggers and similar DB functionalities), otherwise, you need to write your own code for the write part, which will probably need to run raw queries.

As an alternative, you can deal with this at Ruby level by creating all the models (DomainA, DomainB, etc.) manually or with a generator and then creating a common class that acts as an interface. Or, you can create those models with some metaprogramming and again have a common class which work as an interface.