Key differences between MyISAM and InnoDB databases in MySQL

The main differences between InnoDB and MyISAM ("with respect to designing a table or database" you asked about) are support for "referential integrity" and "transactions".

If you need the database to enforce foreign key constraints, or you need the database to support transactions (i.e. changes made by two or more DML operations handled as single unit of work, with all of the changes either applied, or all the changes reverted) then you would choose the InnoDB engine, since these features are absent from the MyISAM engine.

Those are the two biggest differences. Another big difference is concurrency. With MyISAM, a DML statement will obtain an exclusive lock on the table, and while that lock is held, no other session can perform a SELECT or a DML operation on the table.

Those two specific engines you asked about (InnoDB and MyISAM) have different design goals. MySQL also has other storage engines, with their own design goals.

So, in choosing between InnoDB and MyISAM, the first step is in determining if you need the features provided by InnoDB. If not, then MyISAM is up for consideration.

A more detailed discussion of differences is rather impractical (in this forum) absent a more detailed discussion of the problem space... how the application will use the database, how many tables, size of the tables, the transaction load, volumes of select, insert, updates, concurrency requirements, replication features, etc.

MYISAM:

1. MYISAM supports Table-level Locking
2. MyISAM designed for need of speed
3. MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
4. MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
5. MYISAM does not support transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
6. MYISAM supports fulltext search
7. You can use MyISAM, if the table is more static with lots of select and less update and delete.

INNODB:

1. InnoDB supports Row-level Locking
2. InnoDB designed for maximum performance when processing high volume of data
3. InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
4. InnoDB stores its tables and indexes in a tablespace
5. InnoDB supports transaction. You can commit and rollback with InnoDB

In modern versions of MySQL, that is 5.1 and 5.5, you should use InnoDB. In MySQL 5.1, you should enable the InnoDB plugin. In MySQL 5.5, the InnoDB plugin is enabled by default so just use it.
The advice years ago was that MyISAM was faster in many scenarios. But that is no longer true if you use a current version of MySQL.
There may be some exotic corner cases where MyISAM performs marginally better for certain workloads (e.g. table-scans, or high-volume INSERT-only work), but the default choice should be InnoDB unless you can prove you have a case that MyISAM does better.
Advantages of InnoDB besides the support for transactions and foreign keys that is usually mentioned include:

• InnoDB is more resistant to table corruption than MyISAM.
• Row-level locking. In MyISAM, readers block writers and vice-versa.
• Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
• MyISAM is stagnant; all future development will be in InnoDB.

Virtually the only reason to use MyISAM in current versions is for FULLTEXT indexes. And that's due to be supported in InnoDB in MySQL 5.6 (update: indeed InnoDB supports FULLTEXT in 5.6, but it's pretty buggy still, as of 5.6.13).