Re: selecting DB on linux

[Fredric Fredricson <fredric.fredriksson@example.com>]

"Hatsuhiro, Inc." wrote:
> 
> How about MySQL?  I'm interested in it but don't know about the detail.
> 
[ Disclaimer: I'm no expert in these matters but 
  I will try to summarize what little I know on 
  the subject. I am grateful for any corrections. ]

MySQL vs. Mimer or Solid Embedded Engine:

MySQL is good, I guess, but it does not implement 
foreign  keys, PSM (a.k.a. stored procedures) or 
triggers and recently it had no transaction 
management but I believe it has now.

This is probably not a problem for most applications, 
unless you want to open your databases to ODBC access
for external developers. If you do this you have to 
make sure that the data integrity is maintained by 
the database and not by the application and here is 
where foreign keys, PSM and views can be used.

Foreign keys ensures that relations are not broken. 
You can not delete a record in a table that is a 
"foreign key" in another table as long as there is 
a match. Foreign keys are not really necessary to 
maintain integrity, but might make it easier.

PSM (Persistent Stored Modules) or Stored Procedures 
are  routines, or scripts, that takes parameters 
and can update tables in the database. You can give
external developers access to these routines for 
data update and this way you do not have 
to grant access directly to the tables. The stored
procedure can to a lot of sanity checks before the
database is updated and this way it is possible to 
maintain data integrity.

Views are "fake tables" that (usually) are read only. 
A view is defined like a select from potentially more 
than one table and are used like a table.
This is a way to present data in a way that makes it 
easier to understand or use for external developers. It
is also a way to keep consistent interfaces as the
database structure is developed and tables modified.

Triggers are "stored procedures" that are invoked at 
certain conditions such as an attempt to update a 
view or insert in a table. Triggers are considered 
very  error prone and next to impossible to maintain. 
They are really only useful (and necessary) when you 
have an  old legacy application that you cannot modify 
and need to adapt it to a new database.

MySQL vs. PostgreSQL:

When it comes to performance I have seen people state 
that MySQL is about three times faster than PostgreSQL 
(in an article linked to from a PostgreSQL mirror).
On the other hand PostgreSQL does implement PSM (but 
far, far, from standard) and foreign keys.
Personally I find MySQL command line interface better
than both PostgreSQLs psql and Mimers msql.
MySQL does not do sub-selects. Exactly how much problem
this will cause I do not know. Typically "not much" I
imagine.

I think that the basic philosophy behind the MySQL 
and the PostgreSQL projects are different. It seems 
like the MySQL philosophy is "small footprint" and 
"do not implement stuff that is complicated". I am 
pretty sure the MySQL developers are very happy with 
a small, and fast, database that can be used to do 
simple tricks. On the other hand PostgreSQL is out 
to conquer the world and maybe replace Oracle. 
These are obviously generalizations but you get 
the meaning....

Ooops, stepped far into "here be dragons"-part of my
map. Better stop now.

/Fredric Fredricson