[Nottingham]: Relational databases, SQL and PostgreSQL.

[Andy Davidson]

On 12 Jan 2007, at 10:11, Martin Garton wrote:

>> I'll second this.  MySQL's getting better in this respect, but  
>> it's only *just* gotten beyond a toy.  With all due respect to  
>> MySQL fans out there, many people starting using MySQL because it  
>> is popular and free and have no idea how to design a database, how  
>> PostgresSQL can be safer in this respect, and why MyISAM is a Big  
>> Bucket of Suck that most (not all) developers should be taken out  
>> and summarily executed for using.
> I'm no fan of MySQL myself, but I can't think where is possibly  
> falls down on normalisation or referential integrity?  Having said  
> that, I haven't looked closely at MySQL.  I use it when I have to  
> and that's about it.

The MyISAM table space doesn't support transactions, ergo wont  
support foreign key constraints.

Ovid was trolling, though, there are times you may or should want to  
use MyISAM.  Consider a database design for a library application  
which contains a list of books, and then reviews on those books :

The 'book' table might contain an id primary key, the name, the id of  
the author, the dewey-decimal code, and the number of pages.  This  
table is InnoDB, because deleting a book causes problems if, say, it  
is loaned out, so you want to set up key constraints.

However, the official library review might be stored in a different  
table to the one above.  The Third-normal-form fans are likely to  
point out the one-to-one relationship between the 'book' table and  
the 'review' table, and point out what a waste of time this is, but  
storing the prose in a MyISAM means you can now use Fulltext indexing  
- hurrah.  Additionally, read-heavy performance is better on MyISAM  
than InnoDB (sweeping generalisation, but mostly true for large  
tables containing mainly non-integer columns).  MyISAM buys you other  
enhancements like an instant row-count.

Don't fall into the trap of assuming InnoDB is good for everything,  
and MyISAM is good for nothing !  It is *Postgres* that is good for  
nothing.



-- 
Regards, Andy Davidson
http://www.devonshire.it/  -  0844 704 704 7  - Sheffield, UK