Re: Question regarding databases

I'd second Matthew's thoughts about this subject.

We do a lot of develop against quite a few databases in my office. =20
In terms of OSS offerings, we use both PostgreSQL and MySQL.

The former is more mature and more featureful. This latter is (at =20
least historically) easier to get started with.

The usual complaints levied against PostgreSQL relative to MySQL are:

1 - It doesn't run well on Windows.
2 - It doesn't have as nice a collection of client interfaces.
3 - It last the raw select speed of MySQL.

The first complaint was addressed quite some time ago. PostgreSQL =20
now runs as happily on Windows as it does on *NIX, and actually runs =20
quite a bit faster on OS X than MySQL does.

MySQL does have a richer collection of dev tools than PostgreSQL =20
does, but there are still several good ones for PostgreSQL. Stuff we =20=

use around here includes: phpPgAdmin, Navicat (also nice for MySQL) =20
pgAdmin III. I really like the Navicat tool -- it has native clients =20=

for both Mac OS and Windows, but I don't believe they have one for =20
Linux yet.

MySQL is definitely scorchingly fast in some situations -- especially =20=

where you are doing simple selects using the MyISAM table type. But =20
this only really matters if you're doing huge stuff. And you pay a =20
stiff price in losing RI. Still though, MySQL is great in certain =20
situations where speed on simple selects is key.

I guess the summary is that MySQL and PostgreSQL are both decent =20
choices. PostgreSQL _might_ be a little harder to get started with, =20
but it's probably worth the hassle for the features and maturity you =20
gain. That said we're developed some pretty nice apps against MySQL, =20=

so you probably won't go wrong there, either.

If course the fact that Oracle keeps buying key components of MySQL =20
does make me a little bit nervous... :)

//glw

On Mar 21, 2006, at 9:28 PM, Matthew Schumacher wrote:

> 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 =20
> 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 =20
> has a
> bunch more features the mysql, and in many ways compares with =20
> oracle and
> MSSQL server.
>
> Here are two quick examples of where a mysql feature is an =20
> afterthought
> and postgres gets it right:
>
>
> Example 1
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
> =3D=3D
> Mysql does not allow you to use now() as the default value of a =20
> column.
> =46rom 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 =20
> 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=20
> () or
> CURRENT_DATE. The exception is that you can specify =20
> CURRENT_TIMESTAMP as
> the default for a TIMESTAMP column. See Section 11.3.1.1, =93TIMESTAMP
> Properties as of MySQL 4.1=94. "
>
> 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. =20=

> This
> is legit in postgres:
>
> create table test_tab (data varchar(20), timestamp timestamp default
> now() - interval '32.56 minutes');
>
>
> Example 2
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=20
> =3D=3D
> Mysql allows you use the "INSERT ... ON DUPLICATE KEY UPDATE" which =20=

> 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, =20
> 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.
>
>

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