[NTLUG:Discuss] MYSql vs. postgresql

[David]

I'm not trying to say that MySQL is unsuitable.  Rather, there are
some areas where MySQL is less suitable, and the original poster
should investigate PostgreSQL before deciding.



"Real sub-selects", as in the ANSI standard SQL sub-selects, have been
"coming soon" for at least four years.  That lack, and the lack of
progress, is what drove me to explore PostgreSQL, and ultimately switch.

It appears that MySQL has gotten much better in the last few years,
but I'm not sure it as complete an implementation of ANSI SQL as
PostgreSQL is.  With sub-selects, at least, it appears that they're
still not really there.

Often MySQL suggests ways to work around the lack of a feature.  It's
irrelevant.  The ability to work around a missing feature is a poor
second alternative.  Standards compliance promotes database
independence, just as POSIX compliance allows our C code to run on
Solaris or IRIX or HP/UX or Linux.

As for constraints, no, you can't do them with enumerations.
Enumerations work if you know in advance all the possible values a
field may take.  But what if you're talking about assigning people to
offices?  There are several constraints that should be expressed and
enforced:

  * when a person is assigned an office code, there must actually be
    such an office.

  * an office cannot be deleted while there are any people assigned to
    it.  

  * a person cannot be assigned to an office location if that location
    is at or over capacity.

The first restriction depends on what offices exists at the moment,
and so cannot be fixed at database design time with an enumeration.
The second has nothing to do with enumerations at all.  The third
requires a runtime comparison to a computed value.  MySQL can't do
those things (I don't think, I'm not a MySQL guru).

Now it is possible to work around these issues, but that's not nearly
as elegant as having the database enforce the restrictions in the
first place.  And it's not as error-proof.  Implementing the
restrictions as run-time code in Python or PHP is subject to bugs, and
can be bypassed by a database user that doesn't use your consistency
code.  

-- 
David Hayes
david at hayes-family.org