One Relation For Multiple Tables

Yii 1.1 General Discussion
1

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

2

You should not use objects in a database with variable names. This is an anti-pattern for SQL databases. Managing this is a nightmare.

Put it back into one table and use one column as an index.

You could do it this way using table inheritance, this is called partitioning. AFAIK this is supported in databases like PostgreSQL, I don’t know about MySQL.

3

someone told me that even if I use a relation, when I use that relation, Yii will query the database again and get the data;

is this true?

if so, I dont’t need a relation in this case

4

Yii uses lazy loading by default, which can be replaced with eager loading in special cases, when needed. If you’re working with such huge data sets you should be able to profile and optimize your application after writing it. This is possible when the database schema is designed properly.