Yii One relation for multiple tables

Question

I am trying to write a customized relation that uses multiple tables, and this relation neeeds to join to the other tables by using a key from the first table.

Each table has the same columns.

So, let the story begin:

I am developing a online strategy based game. (PBBG - Persistant Browser Based Game)

I had a big table (30.000.000 rows) and I have split that table in 100 smaller tables (300.000 rows in each table).

The action happens within villages.

Details about the villages are stored within the village table.

Each village is situated within a continent, so there is a k number that gives me the continent that the village belongs to.

The village table has the following columns:

id - p.k. x - x position on map y - y position on map k - f.k. continent id name user_id f.k. points

I have 100 continents, that's why i have 100 new smaller tables.

I also have a table where I store the details for all the buildings.

The building details were stored within the village_building table.

So, from the original table village_building i now have village_building_k1, village_building_k2, ..., village_building_k100.

A village_building_k... table has the following columns:

id - p.k. village_id - f.k. building_id - f.k.

Now, I am trying to write a relation that jumps from model village to model village_building_kX where X should change when needed.

If I could use the k value from model village, that would give me the name of the table that i need to join.

If k = 2, the i need to jump from village to village_building_k2 using a concat

public function tableName() {
        return 'village_building_k' . self::$k;
    }

public function relations() {
        return array(
            'relation_village_building' => array(self::HAS_MANY, 'VillageBuildingKXSlaveM', 'village_id'),
        );
    }

$model_village->relation_village_building->building_id

Answer 1

Are you sure, that you need to solve this problem on framework level? I suggest to use here something like http://en.wikipedia.org/wiki/Shard_(database_architecture)