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
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
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
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.