Mailing-List: contact tomcat-user-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <tomcat-user@jakarta.apache.org>
Message-ID: <154b01c21f09$a0cde510$01000001@Will>
From: willh@msoft.com (Will Hartung)
To: "Tomcat Users List" <tomcat-user@jakarta.apache.org>
References: <12b.1366c448.2a4e377b@aol.com>
Subject: Re: The best website database!
Date: Fri, 28 Jun 2002 18:09:33 -0700
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

----- Original Message -----
From: <AMRAN121@aol.com>
> Hi All
>
> I currently use MS ACCESS as my backend to my website and other developers
in
> the forum have advised this is not a good choice and I am aware of this
but I
> was forced to use it as all data comes to me in MS ACCESS from database
admin
> that supply the data for the website. I want to know what is the best
> database to use for a website?

"It Depends"

Now that that is out of the way...

> features I am looking for are:
>
> 1- An excellent GUI (Very important) front like MS ACCESS where I can
quickly
> design queries, tables and so on. I normally design all the queries in MS
> ACCESS and then I just write the single line command "SELECT QUERY1"
rather
> than writing the full query in a javabean or jsp page by hand as this
saves
> me alot of time.

Ideally, you will, in time, wean yourself of this reliance, as most
databases do not have a GUI. In the long term it's better to have a more
intimate relathionship with SQL, minimally for performance reasons. But, it
seems pretty clear you haven't reached that threshold yet.

However, all is not completely lost.

One of the Neat Things(tm) about MS ACCESS is not so much that it has a
built-in database based on Microsofts Jet Engine, but that it can act as a
central access point for, technically, any number of ODBC compliant
databases. So, you can theoretically still use ACCESS as your primary
database, but have all of the tables be linked from the true database host.

This will certainly cost you performance, and the queries you devise may not
work quite like they do in native ACCESS, but it will be close. Particularly
if you are sticking close to the most basic of SQL functionality, and not
relying on a lot of the higher level functions provided within ACCESS.

If you're using mostly "Pure SQL", then ACCESS is simply a data broker, and
yet another layer between your app and your DB.

As long as the contention and locking facilities are being used in the
native backend, versus within ACCESS, you should gain quite a bit of
reliablity over pure ACCESS as well. I'm not totally sure if this is the
case, but it probably is, again for basic SQL statements.

The goal is to use ACCESS simply as an interface into your new database, and
have it broker your SQL statements to and from the database, with hopefully
as little intervention as possible. Your "SELECT QUERY1" will still work,
however, but beware that ACCESS will potentially happily suck in all of the
data from the new DB, churn on it, and then spit it back out to you.
Depending on the query, this can be expensive and isn't what you want. If
you like the simplicity of "SELECT QUERY1" from ACCESS, I would suggest that
you perhaps use ACCESS to develop your basic queries, and then use the
actual SQL generated to turn them into VIEWS on the host DB, so the SQL in
your Java becomes "SELECT * FROM VIEW1".

Again, it's almost always better to use the literal SQL rather than VIEWs,
etc. ESPECIALLY if you're joining them together. "SELECT * FROM VIEW1, VIEW2
WHERE ..." CAN be very expensive. It all depends.

But, when you data or activity gets big enough to actually notice the
performance dogging, this kind of stuff tends to float to the top pretty
quick, so when you need to fix it, you'll find it. These kinds of things
work great with 10 rows in the tables, and die horribly with 10000 rows.

You WILL have issue with BLOBs of any kind, however.

Of course, if you continue to receive data from your admin in ACCESS format,
you will need a way to convert that data into the new back end. Ideally,
this to can be automated from ACCESS as well: INSERT INTO NewBackEndTable
SELECT * FROM OriginalACCESSTable.

So, the point being, that even if you go to a new database, you do not
necessarily have to leave the capabilites of ACCESS behind. Eventually,
particularly as your site gets more loaded, you will want to yank ACCESS out
of the loop and go direct, but it wouldn't surprise me if that's farther
away than you may think. It depends on your site.

As to which backend, Oracle and SQL Server are more than capable. Oracle has
very good direct JDBC support, whereas SQL Server does not, the JDBC drivers
come from third parties. However you're probably using the JDBC<->ODBC
bridge. I've never used it in anger, so can't say anything to its
productiion worthiness, however I've never heard glowing things about it.

But if you continue to talk to ACCESS, you're pretty much stuck with it
anyhow.

Both of these DBs are quite expensive.

mysql's biggest point against it is that it does not conform as well to the
standard SQL syntax and behaviors. It has other issues with it, but many
folks are satisfied and work around them. I don't know how well its ODBC
drivers may work.

PostgreSQL has more conventional SQL DB capabilities, and its own hoards of
raving fans, and decent Java support, so it should be considered as well. I
don't know how good its ODBC driver is either, however.

These two DB's are, of course, free.

Has anyone used the new "desktop" MS SQL database engine? Isn't that the
backend of the Latest/Greatest ACCESS?

You may also want to consider something like SQL Anywhere from Sybase, or
even Interbase/Firebird from WhateverBorlandIsCalledToday. They're cheap,
and not slouches for basic DB stuff. Sure, you can't have clusters of them
running on Mega SMP machines. But I really doubt that's the issue today.

When the modern "desktop" is a 1.5GHz machine, with 512MB of RAM, there's a
LOT of capability in a "workgroup" database server today.

Let's all raise our hands if you've supported over a hundred users on a
33Mhz 68030 with 64MB of RAM? App and DB...Come on..up with the
hands....Anyone?

Anyway, hope that helps.

As said in the beginning, it depends.

Best Regards,

Will Hartung
(willh@msoft.com)


--
To unsubscribe, e-mail:   <mailto:tomcat-user-unsubscribe@jakarta.apache.org>
For additional commands, e-mail: <mailto:tomcat-user-help@jakarta.apache.org>