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

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.

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.

Table partitioning is the way to go. Do not create all those identical table structure.

What table partitioning will give you

1. You will have single table which is logically partitioned by the database.
2. In your applications view, you are querying a single table just like any other database table.
3. In the database perspective, it stores data by partition which is defined by a partition type and partition logic. In mysql, you can refer tohttps://dev.mysql.com/doc/refman/5.7/en/partitioning-types.html
4. Performance benefits if defined correctly. It will avoid scanning the 1 billion rows but instead scan the related partition when doing queries.

Table partition can be very database specific.

A simple example from mysql.

CREATE TABLE employees (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30),    hired DATE NOT NULL DEFAULT '1970-01-01',    separated DATE NOT NULL DEFAULT '9999-12-31',    job_code INT NOT NULL,    store_id INT NOT NULL)PARTITION BY RANGE (store_id) (    PARTITION p0 VALUES LESS THAN (6),    PARTITION p1 VALUES LESS THAN (11),    PARTITION p2 VALUES LESS THAN (16),    PARTITION p3 VALUES LESS THAN MAXVALUE);

The employee is stored to a specific partition to either p0, p1, p2 or p3 depending on which store (store_id) the employee belongs.

You are still accessing it through a single table but the data is stored logically by partition depending on the store_id.

SELECT * FROM employee WHERE store_id = 10

The database will simply look at partition p1 and does not scan other partition (p0, p2 and p3) because simply that query will never find data in those partition.