Magento – How to add extra attributes specific to a product

Ha...this is a good one.
The field in the db that determines the text under the attribute field is note from the eav_attribute table.
I never noticed that you can't change that from the UI.
I've created a lot of attributes that have a 'comment' below the fields but never change one.

To add that you need to specify this in the install script:

$this->addAttribtue('catalog_product', 'attribute_code_here', array(
   ....
   'note' => 'This will be displayed below the field',
   ....
));

To change it for one attribute you can add this in an install/upgrade script.

$this->updateAttribute('catalog_product', 'attribute_code_here', 'note', 'This will be displayed below the field'); 

I can't believe that I've missed this in all the million versions I've used so far.

[EDIT]
See the definition of the meta_description attribute in Mage_Catalog_Model_Resource_Setup::installEntities().

'meta_description'   => array(
                        'type'                       => 'varchar',
                        'label'                      => 'Meta Description',
                        'input'                      => 'textarea',
                        'required'                   => false,
                        'note'                       => 'Maximum 255 chars',
                        'class'                      => 'validate-length maximum-length-255',
                        'sort_order'                 => 3,
                        'global'                     => Mage_Catalog_Model_Resource_Eav_Attribute::SCOPE_STORE,
                        'group'                      => 'Meta Information',
                    ),

This should be what you need:

$product->getResource()->getAttribute('attribute_code')->getStoreLabel();