6

I have seen two different ways of creating a new table in Magento. I want to figure out which one is the best? And why there are two ways? What are the differences between them?

First way

(Reference : http://inchoo.net/ecommerce/magento/magento-install-install-upgrade-data-and-data-upgrade-scripts/comment-page-1/#comment-379299)

$installer = $this;
$installer->startSetup();
$table = $installer->getConnection()
    ->newTable($installer->getTable('inchoo_dbscript/ticket'))
    ->addColumn('ticket_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'identity'  => true,
        'unsigned'  => true,
        'nullable'  => false,
        'primary'   => true,
        ), 'Id')
    ->addColumn('title', Varien_Db_Ddl_Table::TYPE_VARCHAR, null, array(
        'nullable'  => false,
        ), 'Title')
    ->addColumn('description', Varien_Db_Ddl_Table::TYPE_TEXT, null, array(
        'nullable'  => false,
        ), 'Description');
$installer->getConnection()->createTable($table);
$table = $installer->getConnection()
    ->newTable($installer->getTable('inchoo_dbscript/comment'))
    ->addColumn('comment_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'identity'  => true,
        'unsigned'  => true,
        'nullable'  => false,
        'primary'   => true,
        ), 'Id')
    ->addColumn('comment', Varien_Db_Ddl_Table::TYPE_VARCHAR, null, array(
        'nullable'  => false,
        ), 'Comment');
$installer->getConnection()->createTable($table);
$installer->endSetup();

Second way

(Reference : http://www.magentocommerce.com/wiki/5_-_modules_and_development/0_-_module_development_in_magento/custom_module_with_custom_database_table)

<?php

$installer = $this;

$installer->startSetup();

$installer->run("

-- DROP TABLE IF EXISTS {$this->getTable('<module>')};
CREATE TABLE {$this->getTable('<module>')} (
  `<module>_id` int(11) unsigned NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `content` text NOT NULL default '',
  `status` smallint(6) NOT NULL default '0',
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`<module>_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    ");

$installer->endSetup();
7

Both methods have the same result.
The first one you mentioned was introduces in CE-1.6.
In theory that one is the "more correct" than the second one you mentioned second.
Let's say that through some magic Magento supports (natively) other db engine than MySQL.
If you use the first approach in your extensions you won't have to do anything. The extension will work on the other db engine because there should be a driver that transforms the DDL declaration in the query needed for that specific engine.

If you use the second one you might need to write an other install/update script to fit your new db engine.

If you want to be politically correct you should use the first one, but if you don't plan to use something else other than MySQL you shouldn't care.

  • Thanks a lot for the great explanation Marius. I was confusing which one to use. And +1 – Sukeshini Aug 25 '14 at 5:46
  • 5
    Wondering how can I implement "drop table if exist" in first approach. – Adarsh Khatri May 6 '15 at 3:28
  • @Marius, what about "drop table if exist" for the first approach, can you extend your answer with working example, please. Of course if that possible and you did that already. ) – electroid Sep 12 '16 at 3:26
0

Both are good ones for creating new tables except for the first one you need to have more knowledge of the framework. However for altering the tables always use the first one because if the column already exists it will handle the error nicely while second one will throw an exception until you comment out that code.

0

Creating the new table, besides two ways you recommend, I think this way is easy to follow: we have 2 tables:

  • simplenews_category (id, category_name, category_desc, status): save information about news category.

  • simplenews_news (id, title, category_id, desc, status): save information about news

  • TABLE simplenews_news

CREATE TABLE simplenews_news ( id int(10) NOT NULL AUTO_INCREMENT , title varchar(255) NOT NULL , category_id int(10) NOT NULL , desc varchar(255) NOT NULL , status varchar(30) NOT NULL DEFAULT disabled , PRIMARY KEY (id) );

Detail you can take this magento tutorial as reference. Thanks, let me know it can help or not!

  • Where are you going to put this SQL query? – Sukeshini Oct 1 '14 at 10:22
  • In a module: SimpleNews, this module, basically, has features as follows: + Manage news easily + Display news in frontend – hgfihif Oct 2 '14 at 2:24
0

The first method puts Magento in full control. This will work well with any configurations and updates that will affect database.

But the second one might require you to make manual changes since Magento may not be fully aware or in control.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.