How to use multiple database tables in single custom module in Magento

Creating a module which interact with a database table is quite simple. Most of the developers use magento module creator to create such module. But what if you want a module with multiple database tables. Following is the example of module with two database tables.

Step 1. Create setup file of your custom module with following queries.

CREATE TABLE `test` (
`test_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 25 ) NOT NULL
) ENGINE = MYISAM

CREATE TABLE `test2` (
`test2_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 25 ) NOT NULL
) ENGINE = MYISAM

Step 2. Create pool file to register your module under app/etc/modules/Mypackage_Mymodule.xml

<?xml version="1.0"?>
<config>
    <modules>
        <Mypackage_Mymodule>
            <active>true</active>
            <codePool>local</codePool>
        </Mypackage_Mymodule>
    </modules>
</config>

Step 3. Your module configuration file should looks like following

app/code/local/ Mypackage/Mymodule/etc/config.xml
   

<?xml version="1.0"?>
<config>
    <modules>
        <Mypackage_Mymodule>
            <version>0.1.0</version>
        </Mypackage_Mymodule>
    </modules>
    <global>
        <models>
            <Mymodule>
                <class>Mypackage_Mymodule_Model</class>
                <resourceModel>mymodule_mysql4</resourceModel>
            </mymodule>
           
            <!-- model vs db table relation -->
            <mymodule_mysql4>
                <class>Mypackage_Mymodule_Model_Mysql4</class>
                <!-- db table with name test -->
                <entities>
                    <test>
                        <table>test</table>
                    </test>
             <test2>
                        <table>test2</table>
                    </test2>

                </entities>
            </mymodule_mysql4>
        </models>
        <resources>
            <mymodule_write>
                <connection>
                    <use>core_write</use>
                </connection>
            </mymodule_write>
            <mymodule_read>
                <connection>
                    <use>core_read</use>
                </connection>
            </mymodule_read>
        </resources>
    </global>
</config>

Step 4. Now create models Test.php and Test2.php. Here we configure these model with the handler of table test and test2.

/app/code/local/Mypackage/Mymodule/Model/Test.php

<?php

class Mypackage_ Mymodule_Model_Test extends Mage_Core_Model_Abstract
{
   
    public function _construct()
    {
        parent::_construct();
        $this->_init('mymodule/test');
    }
}

/app/code/local/Mypackage/Mymodule/Model/Test2.php

<?php

class Mypackage_Mymodule_Model_Test2 extends Mage_Core_Model_Abstract
{
   
   public function _construct()
    {
        parent::_construct();
        $this->_init('mymodule/test2');
    }
}

Step 5. Now create the resource models for model test and test2. In these files we also set the primary key of both the tables test and test2.

/app/code/local/Mypackage/Mmodule/Model/Mysql4/Test.php

<?php

class Mypackage_Mymodule_Model_Mysql4_Test extends Mage_Core_Model_Mysql4_Abstract
{
    public function _construct()
    {   
        $this->_init('mymodule/test', 'test_id');
    }
}

/app/code/local/Mypackage/Mmodule/Model/Mysql4/Test2.php.

<?php

class Mypackage_Mymodule_Model_Mysql4_Test2 extends Mage_Core_Model_Mysql4_Abstract
{
    public function _construct()
    {   
        $this->_init('mymodule/test2', 'test2_id');
    }
}

Step 6. Create a collection classes so that we can retrieve data from table test and test2.

/local/Mypackage/Mymodule/Model/Mysql4/Test/Collection.php

<?php

class Mypackage_Mymodule_Model_Mysql4_Test_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract
{
    public function _construct()
    {
        parent::_construct();
        $this->_init('mymodule/test');
    }
}

/local/Mypackage/Mymodule/Model/Mysql4/Test2/Collection.php

<?php

class Mypackage_Mymodule_Model_Mysql4_Test2_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract
{
    public function _construct()
    {
        parent::_construct();
        $this->_init('mymodule/test2');
    }
}

So, now you have a custom module with two tables. Your module can interact with these tables with their models and respective collections as follows :


$testModel = Mage::getModel('mymodule/test')
    ->setName("abcd")
    ->save();

$test2Model = Mage::getModel('mymodule/test2')
    ->setName("abcd")
    ->save();

Hope this will help you.