Re: Question regarding databases

Tony wrote:
> Hi Everyone:
>
> I have a question. I am setting up a database and I
> want this database
> to be accessible via a web browser to both input the
> data and retrieve
> the data.
>
> My choices are SQL, MySQL, Oracle, microsoft Access,
> and who knows what
> else.
>
> Does any one have a recommendation as to which
> database software is best
> for a database warehouse?
>

Here are my thoughts on those databases:

1. MsSQL, not a bad database really, but it's expensive, a pain to
backup, needs it's on box to run on, and has a few security issues. On
the flip side, it's one of the easiest to manage.

2. MySQL. It does store and fetch your data, but almost every real
feature in it is an after thought and where you do have support some
features they are almost always limited to the common use of that
feature. See my examples below.

3. Oracle. Larry Ellison is the greediest man on the planet.

4. Microsoft Access is not a real database. Run away, run away!!

I recommend postgres. I have been using it for a few years and it has a
bunch more features the mysql, and in many ways compares with oracle and
MSSQL server.

Here are two quick examples of where a mysql feature is an afterthought
and postgres gets it right:

Example 1
========================================================================
Mysql does not allow you to use now() as the default value of a column.
 From their docs:

"The DEFAULT clause specifies a default value for a column. With one
exception, the default value must be a constant; it cannot be a function
or an expression. This means, for example, that you cannot set the
default for a date column to be the value of a function such as NOW() or
CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as
the default for a TIMESTAMP column. See Section 11.3.1.1, “TIMESTAMP
Properties as of MySQL 4.1”. "

So they work around this major shortcoming by giving people the
CURRENT_TIMESTAMP keyword for the timestamp column so at least the
people wanting an automatic timestamps are happy. Basically they add
just enough support to do the most common thing.

Postgres, allows all of the functions in a create table statement. This
is legit in postgres:

create table test_tab (data varchar(20), timestamp timestamp default
now() - interval '32.56 minutes');

Example 2
========================================================================
Mysql allows you use the "INSERT ... ON DUPLICATE KEY UPDATE" which gets
around the problem of inserting or updating depending on whether the
data is there, where postgres gives you a complete rule system which
allows you to:

CREATE RULE update_rule AS ON UPDATE TO test_tab WHERE EXISTS (select
key from test_tab) DO INSTEAD insert....

It may seem that postgres is more complex, and in some ways it is, but I
would much rather have a complete rule system then support for a few
common things everyone wants to do.

Hopefully this information will help,

schu

---------
To unsubscribe, send email to <aklug-request@aklug.org>
with 'unsubscribe' in the message body.
Received on Tue Mar 21 21:29:16 2006