AR problem. Silly solution is found but I want to Yii !

Yii 1.1 General Discussion
1

Imagine a situation like the schema in the picture attached to this post

I would like to get the model Step1 which is linked with PvPlant model

Put as you see I have to pass from ProcedureStep model.

The difficult part is that PvPlant has many ProcedureStep models

but each ProcedureStep model has only one Step1 model

The following code works although I am not getting advantage of the yii AR features. Do you have a more "correct" way?




$procedureSteps = $models["PvPlant"]->procedureSteps;

		

foreach($procedureSteps as $procedureStep)

{

	if( ($model=$procedureStep->step1) )	break;

}

Maybe something with the "with()" method ?

In case there is a tutorial for AR please link me to that to not bother the forum with technical stuff :)

Thank you!

2

Why would you create the tables step1 and step2? How much do you know about database normalization ? Does that scheme seems logic to you?

Why not having just two tables: pv_plant and procedure_step and make procedure step table like:

procedure_step

|

|–>id

|–>pv_plant_id

|–>execution_time

|–>step_number

|–>nominal

|–>protocol

And make a one-to-many relationship between the tables, that way you can easily retrieve any kind of related records between the tables.

3

First of all let me say that I corrected a small part of my code, since it was a lazy copy-paste and not very consistent with my example ;D

The example that you see on my picture is not the real schema. It is only a fragment of it!

Now imagine multiple steps, much more than two!

Each step could be filled months later => result is that I would have a lot of rows with empty columns (null values)

I could have used Excel for that… not a database :P

Correct me if I am wrong but I think the procedure step (please note that it has many attributes not just a few, as in my example) with the step1, step2, step3, etc. is an ISA hierarchy.

Yes I know that one implementation for an ISA hierarchy is to have a single table for every table inside the hierarchy (in fact I found a wiki inside yiiframework.com for that)

Also another way is to have different tables for the parent class and different tables for the children classes.

I was thinking that maybe I will stumble upon the same situation with ISA hierarchies many times and maybe I should extend CActiveRecord for these parent classes, to handle things like ParentClass->child where child would dynamically one of the children classes… or try another solution, not sure yet :)

Which are your suggestions?

4

That’s the point(matter a fact, this answer should say everything), if you’d have 100 steps would you create 100 tables ?

See, at a point it doesn’t make sense anymore.

And what if you will have many empty(null) columns ? This doesn’t matter, actually the number of a table columns doesn’t matter at all as far as you select only the columns you need in the query(ie:don’t use SELECT * FROM …).

Please keep in mind that table joins are very expensive and far more expensive when done wrong.

The database normalization stuff is far to much to be discussed in a single topic and i am no db architect at all, but i can speak from my own experience when i say that you might want to take into consideration redesigning the tables so that you can reach the data easily.