From: Thimble Smith
Date: March 12 1999 6:22pm
Subject: Re: Some questions/concerns about MySQL
List-Archive: http://lists.mysql.com/mysql/146
Message-Id: <19990312112242.S28088@desert.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

On Fri, Mar 12, 1999 at 12:05:40PM +0100, Eric Maryniak wrote:
> ---snip---
> The problems are:
> 
> 1. No Sub-selects, which is very awkward, but can be surpassed in www
> applications, but they will be more complicated and will have more
> unnecessary fault possibilities and become bigger.

Dat is wel een probleem nu, maar 't gaat straks weg gaan.  In 3.23
subselects will be fully implemented.  It is a few months away, maybe,
from alpha release.  It sounds like you are planning for the future,
and it might be too hasty of you to count this against MySQL since in
the very near future it will no longer be true.

> 2. Transacties, foreign keys, stored procedures, triggers en commit
> rollback are not supported. In other words referential integrity is not
> supported. Because the NIWI databases are primarily meant for (online)
> input and mutation of records this is a very fundamental problem.
> In fact NIWI doesn't do anything else as (online) transaction
> processing.
> For instance how to maintain a multithesaurus database that is used to
> index several objects in the databases without referential integrity
> implemented in the database engine.

The main problem I have with no transactions is - what happens if the
database server crashes.  All of the other uses of rollback/commit can
be handled pretty easily in applications.  Sure, if you have bugs in
your code they will do less damage probably if you have referential
integrity built into the database; but with or without ref. integrity
support in the database your application code typically has to do the
same checks (esp. with input from a web browser, since you have so
little control over what people type into the form fields).

> 3. No views make life very complicated.

Ja, dat is wel een probleem.  I don't know when MySQL is going to have
views support.  Views on a single table should be pretty easy to make
and if you support MySQL heavily you could probably convince them to
implement more complex views sooner than they might do otherwise.

> Conclusion: MySQL is a possible candidate (if it can search better and
> faster) to replace the NIWI search engine software PLweb. Because the
> data are stable. But that means unnecesarry  replication of data. 
> Nicer would be of course a direct web interface to the database in which
> the data are maintained.

PLWeb is a vastly different beast: it makes me wonder if an SQL database
really is the best solution for you, if you've been using PLWeb.  But
maybe what you're saying is that PLWeb wasn't the best tool, and an SQL
database is better.  But PLWeb is great for searching text (with field
structures) - fast and powerful.  If a lot of your data is text, you
might want to stick with something like that or do some kind of a
hybrid solution (use SQL for relational data, and PLWeb for textual data).

> Online database applicaties become unnecesarry heavy and unreliable.
> Which is shown in the suggested solution for the deleting of
> mother-child related records on page 240 of the manual. Here the mother
> is killed before the children are removed. In case of problems there
> will be no way to find the children because the motherrecord is already
> deleted.

As long as your applications don't crash while deleting, you can avoid
these problems.  It's just if your app dies after deleting some rows
but not all - then you might wind up with orphan rows.  I think there
are almost always solutions to this - but they might be more complex
than you want to deal with.

> In a  good database I have only to implement refential integraty once
> when designing the database and that is independant of the application
> for which I use the database.

Yes, but all of the applications still need to be aware of it and
handle the errors, etc.  Just printing a database error that says,
"Can't insert the info you gave me because it violates referential
integrity" isn't a very friendly user interface.  I'm sure you know
this, so I'm sorry if I'm sounding silly.

I've made the decision to work with MySQL in large part due to the
dedication of the development team.  They have consistently shown
that they are interested in making the best product possible.  They
respond to requests in a very reasonable way.  The database server
is getting better and better with each release.  I'm not going to
say that you'd be wrong to go with another database server, but I
think that, if you can get by without views and referential integrity
checks, the MySQL server is a wonderful product/community.

Tim