DB structure and then?

Hi Yii community,

My scenario: I’m trying to do some kind of private finance app with an investment being tagged with several categories (short-term, long-term) and being of one of several investment types (shares, insurance). DB-wise general data resides in the investment (start and end date), type-specific data in an e.g. insurance table (insured person).

Now, I have my DB set up and I’m wondering for which tables do I need which structure elements (model, controller)?

(I already consulted the tutorial as well as the Definitive Guide:developemnt workflow)

As I do not know how everything belongs together or could be split into separate topics this post got a bit lengthy. Sorry for that.

1. Categories/Tags (n:m relation with investment via linking table)

a. I read in a tutorial that all data manipulation is done by the model. So, is it correct that for every table I have I should create a model?

Looking at the Yii tutorial confuses me:

protected function afterSave()

{

    if(!$this->isNewRecord)

        $this->dbConnection->createCommand(

            'DELETE FROM PostTag WHERE postId='.$this->id)->execute();

 

    foreach($this->getTagArray() as $name)

    {

        if(($tag=Tag::model()->findByAttributes(array('name'=>$name)))===null)

        {

            $tag=new Tag(array('name'=>$name));

            $tag->save();

        }

        $this->dbConnection->createCommand(

            "INSERT INTO PostTag (postId, tagId) VALUES ({$this->id},{$tag->id})")->execute();

    }

}

b. In this example they actually do not create a Tag model, but address it with Tag::model(). How is this possible?

c. Would you suggest for my categories to create a model for the categories and the linking table or use plain SQL as done in the tutorial?

2. Types

Let’s assume I want to show a detailed view of an insurance with data all the way down to tiny and updated bits. What would you consider a clever way to include data from the specialized tables in a query?

a. One option is to create models for each special table and add a relation to the investment model for each investment type table, correct?

b. So, insurance depends on investment. Adding options depending on the insurance and a current state depending on the option, am I right when I think of it as cascading relations defined in the respective upper level model?

3. Controller

Would you use more than one controller (investment) for this part of the app? (There are no direct user requests planned addressing these sub tables)

I really appreciate any comments or suggestions regarding the topics mentioned or fundamental errors in my reasoning you might have discovered.

cheers

Steffen

1a. Yes, generally you should create a model for every table (there can be a few exceptions though)

1b. The Tag::model is a Model but the code is just showing the relevant code for afterSave functionality.

1c. I’d go with a Model first unless you know you’re gonna get some performance benefit or some sort of simplicity out of the other.

2. Depends on what the specialized data is.

2a. Yes.

2b. Yes you can think of it that way.

3. Yes I think I’d use one controller. I’m sorta testing the waters right now with using one controller (on a project management type app). On the one hand working with a huge file can get to be rough, but also working with multiple controllers can mess you up too. Ideally I think I’d like just one controller that calls functions elsewhere in some other classes that do the grunt work.

Lucas

[RESOLVED]

Lucas, thanks for your reply.

After having set up the basic webapp and one controller, the whole model concept and oop terminology gets clearer.

In a first step nested AR queries really seem to work. I already wonder if those chained query paths can still be handled if the app grows bigger.

So far, one central controller looks quite good. I’m planning now for a few secondary controllers e.g. for user management and for some standalone master data tables.

steffen