Sometimes it is useful to add some fields to the attribute main table (f.e.: IDs from external systems like a middleware or a PIM). In the following example you can see how to add ID-fields to the category entity. Therefore I created a setupscript as an instance of Mage_Catalog_Model_Resource_Setup.

• Setup-model declaration in config.xml:

  <?xml version="1.0"?>
  <config>
      <modules>
          <Sheldon_Module>
              <version>0.1.3</version>
          </Sheldon_Module>
      </modules>
      <global>
          <models>
              <sheldon_module>
                  <class>Sheldon_Module_Model</class>
                  <resourceModel>sheldon_module_resource</resourceModel>
              </sheldon_module>
              <sheldon_module_resource>
                  <class>Sheldon_Module_Model_Resource</class>                
              </sheldon_module_resource>        
          </models>        
          <resources>
              <sheldon_module_setup>
                  <setup>
                      <module>Sheldon_Module</module>
                      <!-- #### Setup Model instance type declaration #### -->   
                      <class>Mage_Catalog_Model_Resource_Setup</class>
                  </setup>
              </sheldon_module_setup>
          </resources>
      </global>
      <!-- ... -->
  </config>

• Setup-script:

  <?php
  /* @var $oInstaller Mage_Catalog_Model_Resource_Setup */
  $oInstaller = $this;
  
  $oInstaller->startSetup();
  
  $aAttributes = array(
      'tb_id' => array(
          'label'             =>  Mage::helper('sheldon_module')->__('TB-ID'),
          'required'          =>  false,
          'type'              =>  'static', //TO BE FOUND IN THE MAIN-TABLE (f.e.:category_entity)
          'input'             =>  'text',
          'group'             =>  'Tradebyte',
          'global'            =>  Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL,
          'visible'           =>  1,
          'unique'            =>  1,
          'frontend_class'    =>  'disabled',
          'note'              =>  'Unique tradebyte category id',
          'sort_order'        =>  1,
          'input_renderer'    =>  'sheldon_module/adminhtml_catalog_form_renderer_element_text_disabled'
      ),
      'tb_parent_id'=> array(
          'label'             =>  Mage::helper('sheldon_module')->__('TB-Parent-ID'),
          'required'          =>  false,
          'type'              =>  'static',
          'input'             =>  'text',
          'group'             =>  'Tradebyte',
          'global'            =>  Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_GLOBAL,
          'visible'           =>  1,
          'frontend_class'    =>  'disabled',
          'note'              =>  'Unique tradebyte category id of parent category',
          'sort_order'        =>  2,
          'input_renderer'    =>  'sheldon_module/adminhtml_catalog_form_renderer_element_text_disabled'
      ),
  );
  
  
  $iCategoryEntityTypeId = $oInstaller->getEntityTypeId('catalog_category');
  
  $sMainTable = $oInstaller->getTable('catalog/category');
  $oConnection = $oInstaller->getConnection();
  
  foreach($aAttributes as $sCode => $aConfig){
      $oConnection->dropColumn(
          $sMainTable,
          $sCode
      );
      $oConnection->addColumn(
          $sMainTable,
          $sCode,
          'int(11) default NULL'
      );
      $oInstaller->addAttribute($iCategoryEntityTypeId, $sCode, $aConfig);
  }
  $oInstaller->endSetup();
  
  // mark index as "reindex required"
  $indexerCodes = array(
      'catalog_category_flat'
  );
  
  $indexer = Mage::getModel('index/process');
  foreach ($indexerCodes as $code) {
      $indexer->load($code, 'indexer_code')
          ->changeStatus(Mage_Index_Model_Process::STATUS_REQUIRE_REINDEX);
  }
  
  //clear EAV Cache:
  $this->cleanCache();

   

Hint: if you want to save the attribute via $oProduct->getResource()->saveAttribute($oProduct, $mCode);  you have to define a backend-model which extends Mage_Eav_Model_Entity_Attribute_Backend_Abstract (or even Mage_Catalog_Model_Product_Attribute_Backend_Sku).

Otherwise you will run into errors like:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'catalog_product_entity.value_id' in 'field list', query was: SELECT `catalog_product_entity`.`value_id` FROM `catalog_product_entity` WHERE (entity_type_id=4 AND attribute_id='176' AND entity_id='2308')