[Zope] Which database?

[Phillip Hutchings]

On Wed, 1 Dec 2004 09:51:52 +1300, Phillip Hutchings <sitharus at gmail.com> wrote:


> Jonathan Cyr wrote:
> > Gosh... Never Ever... I guess MySQL should close up shop...
> >
>
> Nope - they have a useful product, but it (the original, not the SAPdB
> derivative) should not pretend to be an RDBMS. More than any other
> product, these guys have confused the issue of what features are
> required for a coherent RDBMS product. Their past lack of support for
> referential integrity has been pretty scary on that front.
>
> I appreciate not every one feels as strongly about this...

I disagree - not every project needs a DBMS that supports ACID
transactions and foriegn key integrity checking (which, by the way,
MySQL has supported for a couple of years at least if you use InnoDB
tables instead of MyISAM). In 99% of small web applications, and even
some large ones, the integrity checking can either be done on the
client side, or even ignored all together (a really _bad_ idea). If
you're dealing with 5 tables it really isn't hard, and if you're just
using the database as a backend to an object-relational mapping system
you can avoid many problems.

Of course, there are times when they are needed. The important thing
here is to choose the DBMS that is best suited to your needs. If you
will be doing few inserts and many simple selects, and don't need your
DBMS to manage foreign keys for you, then use MySQL. If you're going
to deploy (or can foresee youself deploying) several applications that
use the same system then you can save yourself many headaches by
choosing one that does.

> > You should hit the MySQL, PostgreSQL, etc. mailing lists, and ask a few
> > questions with your needs in mind.
> >
> > Spend some time outlining your needs, and you'll reap the benefits down
> > the road...
> >
>
> Agreed, x2!

You can make that x3, but if you choose the wrong one now you will pay
the price. My basic rules of thumb are like this:
Basic web app (INSERT/UPDATEs rare, many simple SELECTs): MySQL/MyISAM
App that needs ACID/relational integrity (more complex app, frequent
updates, maybe more complex selects): MySQL/InnoDB
Complex app that has multiple clients (web/desktop etc. Here stored
procedures and referential integrity are real benefits): PostgreSQL.
But I have no experience with Firebird.

Just as a reference point, one of the busiest sites I know the backend
of - Livejournal.com - uses MySQL. They're transitioning to InnoDB at
the moment. On their system the critical feature was cost, rather than
supporting advanced features. Anyway, on their current deployment they
couldn't use any real integrity checks...


--
Phillip Hutchings
http://www.sitharus.com/
sitharus at gmail.com / sitharus at sitharus.com