Magento resource model for table with compound primary key

Question

I am creating a custom module for a Magento ecommerce site, and the module will center around a new (ie, custom) table that has a compound/composite primary key, or rather the table has two columns that make up the primary key. Does anybody know how to create your models/resource models based on a table with a compound key?

To give a few more details, I have looked up several tutorials and also used the excellent moduleCreator script. But it seems like all the tutorials revolve around the table having a PK with just one column in it. Something like this:

class <Namespace>_<Module>_Model_Mysql4_<Module> extends Mage_Core_Model_Mysql4_Abstract {
   public function _construct(){
        $this->_init('<module_alias>/<table_alias>', '<table_primary_key_id>');
   }
} 

Also, I just noticed that looking at the database model almost all tables have a single primary key. I understand this has much to do with the EAV-style db structure, but still is it possible to use a table with a compound PK? I want to stick with the Magento framework/conventions if possible. Is it discouraged? Should I just change the structure of my custom table to have some dummy id column? I do have the ability to do that, but geez!

(Another side note that I thought I would mention is that it looks like the Zend Framework provides a way to base a class on a table with compound primary key (see Example #20 on this page - about half-way down), so it seems that the Magento framework should also provide for it... I just don't see how.)

Answer 1

Like most Active Record inspired models, Magento's Mage_Core_Model_Abstract wasn't built with support for composite primary keys in mind. Any models that inherit from this base (meaning all of them) inherit this assumption. If you want to use composite primary keys, you won't be able to. Your choices are to go the Magento Model route and create a single primary key ("fake", as you called it) and then apply a unique index to the table, OR implement you own Model layer using the base Zend DB Table, OR import a third party model solution into the system that supports the features you want.

As far as Zend Framework goes, the Magento team used Zend's Table Gateway Pattern to implement an Active Record style Model layer for their framework. Zend Framework isn't an application stack like Cake or Rails, it's a collection of class libraries that can be used to build application stacks (or applications, or lots of other things). Just because something is supported in the Zend Framework classes doesn't mean that systems and applications using the Zend Framework get it for free.

Answer 2

see that magento Model “Mage_SalesRule_Model_Resource_Coupon_Usage” , the table 'salesrule_coupon_usage' has a compound/composite primary key. It's like this:

protected function _construct()
{
    $this->_init('salesrule/coupon_usage', '');
}

Answer 3

While you may not be able to use a compound primary key in the standard Active Record style Models, one can easily create a compound primary key in a supporting database migration by setting each field to primary;

/**
 * Create table 'cms/block_store'
 */
$table = $installer->getConnection()
    ->newTable($installer->getTable('cms/block_store'))
    ->addColumn('block_id', Varien_Db_Ddl_Table::TYPE_SMALLINT, null, array(
        'nullable'  => false,
        'primary'   => true,
        ), 'Block ID')
    ->addColumn('store_id', Varien_Db_Ddl_Table::TYPE_SMALLINT, null, array(
        'unsigned'  => true,
        'nullable'  => false,
        'primary'   => true,
        ), 'Store ID')
    ->addIndex($installer->getIdxName('cms/block_store', array('store_id')),
        array('store_id'))
    ->addForeignKey($installer->getFkName('cms/block_store', 'block_id', 'cms/block', 'block_id'),
        'block_id', $installer->getTable('cms/block'), 'block_id',
        Varien_Db_Ddl_Table::ACTION_CASCADE, Varien_Db_Ddl_Table::ACTION_CASCADE)
    ->addForeignKey($installer->getFkName('cms/block_store', 'store_id', 'core/store', 'store_id'),
        'store_id', $installer->getTable('core/store'), 'store_id',
        Varien_Db_Ddl_Table::ACTION_CASCADE, Varien_Db_Ddl_Table::ACTION_CASCADE)
    ->setComment('CMS Block To Store Linkage Table');
$installer->getConnection()->createTable($table);

Answer 4

I know this is an old question but i had the same issue , but here how i fixed it : i added all the fields separated by comma :

protected function _construct()
{
    $this->_init('salesrule/coupon_usage', 'first_field,second_field,...');
}