Databases - What's the difference

I believe that MySql has very limited support for sub-queries and other advanced query techniques.  That is simply not acceptable at a large company.  The additional cost in developer time to work around database limitations costs far more at a large company than the database software itself.  

I know nothing about PostgreSQL.  That is probably true for a lot of DBAs.  That is another reason companies don't use it.  The difficulty of finding employees to work on those databases and the cost to train new employees again are more than the cost of database software.  Oracle is very expensive, but it is extremely powerful and fast and you can find plenty of people that already know it and can be useful in your shop almost from their first day of work.  Even assuming that a good DBA or developer would accept a job in a PostgreSQL (or other lesser-used database) shop (not particularly good for the resume), they would need a lot of time to fully understand that database.

Yeah - maybe it's true that a lot of people know Oracle - but in my case - I've only used mySQL - everybody I know, don't know something about Oracle (only that it's a big DB-server) and more about mySQL?

What precisely can you do with an Oracle that you cannot do with a free server such as (let's take starting point from mySQL) mySQL or for that case a PostgreSQL? It maybe could be the Borland (Inprise) Interbase?
What's so special about Oracle (besides it's commercial and easy to get employes who know something about)? Okay  -  maybe I'm not a special advanced mySQL-user, but what kinda sub-queries and advanced query techniques does Oracle support then? Why should I (or my company) invest in a Oracle system (theoreticaly)?

angelIII: "It would be senseless to enumerate everything, but by only reading all the products from Oracle, you can see wich dimension of that solution can take"
Well for me as a user it's not senseless. I need somebody to tell me what I can do with an Oracle system that I cannot do with another free DB-server (thinking of mySQL)?
Is there any GUI for the DB-server like Microsoft Access (not to compare the two database-systems - only gui-info) - which fx. mySQL doesn't have? That would be nice to know!


Really hope someone can be specific about why Oracle is a better system!! That's the big question for me.

There's no such thing as a "better" system.
There's only a system fit for the application/function to perform.

MySQL and Access will perform well for the common small business applications, but when you need a worldwide system for a fortune 100 company, they're just not suited.
Things like StoredProcedures/BackupRecovery/etc. are just much more reliable on a DBMS like Oracle, Adabas, DB2, Sybase, etc. then on MySQL and Access.

It's like comparing a Volkswagen Beatle and a Rolls Royce, both will bring you where you need to be, there is however a difference...

Nic;o)

Nico5038 -_>
Yeah - maybe it's like comparing a Rolls Royce with a Volkswagen Beatle - but I need to compare this - cause I don't know the difference and would really like to know why I should learn Oracle (and therefore invest in the system)..
As a DB-server the mySQL is almost as fast as Oracle 9i - the difference is actually really small!! So what functions are accessible on a Oracle system.. I don't think it's difficult making a backup on a mySQL system (just copy the DB-files - that's easy - the best backup-way there is)... But Nico - you actually answered some of my question - but as a small user I don't know what StoredProcedures is? A stored procedure (the name says itself) - but what's the usage capabilities in this?

In Oracle you can trigger statements when a row is inserted, updated or deleted. This is enabling you to do e.g. a 11-proof on a bank account number before inserting the row. Thus the "business rules" are more or less like a shell around the data and the data-integrety is protected against program's that manipulate the fields in a wrong way and it saves the coding of such a check in all application programs.

I must also say that the speed of retrieving data isn't the only concern when you manipulate databases. When in use, the database will need to be reorganized from time to time and the indexed and space allocation needs to be guarded.

Besides the "full backup" you mention to be easy, there's also the possibility to use transaction log's and to undo a whole session of updates to return to a previous "stabile state" when a program did some wrong updates.

Looks like you have some "fear" to learn this new DBMS. I must admit that I always see it as a challange to learn a new product. It will enable you to compare the strong points of the "old" product with those of the new and as these are both DBMSses, but you would see a lot of simularities too. That will enable you to learn faster as others without DBMS experience!

Success !

Nic;o)

Hey again Nico

You're actually a nice guy - you're the first one to answer some of my questions!!

I read about Stored Procedures on mySQL's web-site - It looks like it gonna be implemented in the mySQL 5 version. That's actually nice.

What I didn't know was that the Oracle actually could undo some queries if a program did something wrong - that's nice enough..  I don't know anything about this for web-usage (but if you write the correct SQL it should do anything *thinking of me as a GURU* *LOL*.. I know it's a smart thing.

About logging - I don't know the logging system in mySQL, there should be something - but if it as good as Oracle's I don't know!!!

Yeah - the SQL-syntax is almost the same in the different DBMS's (fortunately)... So it shouldn't be any problem learning af new one.
I don't have a fear about learning new DBMS's (I'm just a student so have plenty of time learning it)...

About writing to the database - like any other DB - you do also have to write the programs that control the dataflow to the database. It's not like there's any kinda GUI included with Oracle that'll make it easier making some forms fx. by using the Java language?


Well - if you have any other nice things to tell about Oracle - let me know cause know I'm getting somewhere!! StoredProcedures, Logging-options and Undo-posibilities can't be all to say that's nice on a Oracle DB?

Glad to see you're getting a bit "optimistic" ;-)

But every thing comes with a "cost"...
The Stored Procedures (Called Triggers in Oracle) are nice to "protect" the data-integrety, but when you need to fill your tables with test data it's a big hurdle. You'll need to fill the tables in the correct sequence and all data needs to be correct. I've spent many hours puzzling how to get the data in ;-(

The filling of the tables can be done by creating forms using Oracle Forms, but you could even use access forms with an ODBC connection to do so. I personally use for small manipulations always access with (ODBC) linked tables. It enables me to link text, excel, MS SQL, Oracle, etc. and just use the Access graphical query editor as I'm too lazy to type all those fieldnames ;-)

Oracle also has the "Developer" part that will enable you to define a system and have large parts of the system generated. I think you'll find it a bit confusing to start with, but when you get the hang of it you'll see that it's very well suited for team development of larger systems.
Just pay much attention to the different architecture schemes, then you've a good referential grid for the many Oracle parts there are.
And clicking the EE-sponsor link would already take you to the place where the action is ;-)

Nic;o)

Thanks Nico...

What does an Oracle single-user edition actually cost? I can't find any prices at the web-site!!

Maybe I should start looking around for learning Oracle and taking some starting-courses!!


Thanks for all your help...

Btw. - I hate the MS Access - Couldn't you use another frontend when being lazy!!

I don't know the prices either (it's already installed by the client when I work with it)

But I guess there will be enough companies looking for people wanting to work with Oracle and supplying you with the needed training. (I also started as an apprentice programmer that way)

Nic;o)

BTW, Just check the Access topic area top 15 to see why I use it ;-)

Okay Top 1 list you could say!!! *LOL*

Yeah - but another (maybe free) gui would also be nice :) for the end user!!!

I know, but all clients I've worked for untill now have office installed with Access, thus making it in general easy "accessable". Just using it's tables and query editor will make data manipulation and extraction easy.

When looking for a free GUI you should look at e.g Delphi with the Borland alternatives. The "binding" if the data with the forms is however some more work as within Access but from an OO point of view it's certainly "better" constructed.
As Delphi also supports ODBC, you can link to almost every other DBMS, including MySQL.

Nic;o)

Hi Greben,

I am an Oracle Developer/DBA.
I have ever used Sybase and some other desktop DBs before but have never used MySQL.

Here I can list some important differences between Oracle database and other DBs for you:

1. For application developers:

   (1) Oracle supports stored programs such as stored procedurs, triggers, packages,...
This feature enables you to centralize your application logic in the database regardless whatever clients are.
You can also get better performance,managability, as well as integrity.
   (2) Oracle SQL and functions are powerful.
Oracle provides powerfule functions and subqueries in its SQL statements.
This feature saves much cost for developing complex queries.
   (3) Oracle supports distributed databases.
You can develop distributed systems by database links, materialized views, and distributed queries.
If your application creates distributed transactions, Oracle handles two-phase commit for you automatically.
This feature saves much cost for developing distributed systems and the applications get lower complexcity.
   (4) Oracle supports partitioned tables/indexes.
This feature is important for developing Datawarehousing/DSS systems.
The disk I/O workload can be well balanced and thus get better performance/recoveribility.
   (5) Oracle provides many build-in utilities for developers,
such as File I/O , TCP , SMTP , HTTP, Dynamic SQL, Job scheduling ...
For example, you can send e-mail in a stored procedure by calling the UTL_SMTP package.
These build-in utilities can save much development cost.
   (6) Oracle supports Java in the databse.
Java is a popular language, and you can get many external resources/libraries for your application.
In fact, the SMTP, HTTP, TCP build-in utilities are implemented by Java.
   (7) Oracle database is well integrated with its middle tier(iAS) and developer products.


2. For DBAs:

   (1) In addition to full backup, Oracle supports many other backup options.
These advanced backup options do not require database shutdown for backup operations.
This feature is important for 24*7 systems.
   (2) For a well managed database,in most cases, a disk crash do not require databse shutdown for restore/recovery.
Only damaged tablespaces/datafiles are not available for the users.
The other parts of the database can be accessed normally.
This feature provides much higher availibility and is important for many critical systems.
   (3) Oracle supports many advanced recovery options.
For example, by performing an incomlete recovery, you can recover your database state preior to the
running of a batch job which causes an accidental mass error deletion of customer records.
   (4) Oracle supports many performance tuning utilities and statistics, which is useful
to locate the system bottlenecks and then tuning.

There may be many features I'm missing in the list.
All I want to tell you is that you will enjoy Oracle when you are getting familiar with it, especially when developing large/complex systems.

Albert.

I've been studying a little bit around.. And actually mySQL has transaction safe tables using the innoDB or BDB table type.
Quote from the mySQL table type specification: "InnoDB is currently (October 2001) used in production at several large database sites requiring high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. stores over 1 TB of data in InnoDB, and another site handles an average load of 800 inserts/updates per second in InnoDB."

Well as I see it, the InnoDB table in mySQL is actually quite good and takes care of the "undo-processing"..

"If your application creates distributed transactions, Oracle handles two-phase commit for you automatically" - distributed transactions and two-phase commit are new words for me - please explain?

mySQL doesn't have support for StoredProcedures (I know that - but that can't make people choose one product from another!)

As you probably can hear - I'm very fond of freeware :) But I really wanna know why is Oracle SO MUCH better!

We are getting somewhere - but there's just to little difference to me (yet)!!!

Btw. as a new thing - mySQL has javasupport with the JDBC-interface using mm.sql :)

Hi Greben,

This is an explaination for distrubuted transactions and two-phase commit:
1. A transaction is a set of data changes which have to be
   either all commited or rolled back.
   For example, a sales order entry program may include the following steps:
       (1) Check customer credits and lock the record. (SELECT/LOCK from CUSTOMER_CREDITS)
       (2) Insert into order header table. (INSERT into ORDER_HEADERS)
       (3) Insert into order lines table.(INSERT into ORDER_LINES)
       (4) Update customer credits.(INSERT/UPDATE CUSTOMER_CREDITS)
   These steps have to be either all commited or rolled back.

2. Now, what if one table resides in a database other than
   the other tables ? That is, what if CUSTOMER_CREDITS is in database A
   and other tables are in database B, database A and B are reside in different network nodes?
   In this situation, this transaction is distributed among two(or even more) databases.
   A commit/rollabck request from a client application have to be processed in two(more) databases.
   Without distributed transaction/two-phase commit support,
   application programs have to handle all these things...
   Image that when you have successfully committed in database A, but just before commiting in database B,
   a network failure causes the order entry program crashes.
   The customer credit record is permanently changed in database A,
   but sales order records are lost(rolled back) in database B.
      --- The data integrity is broken.

3. In Oracle, distributed transactions are totally transparent to application programs.
   Application programs can do their things as if they are performing in a single database environment.
   A SQL statement can even join tables from many databases.

------------------------------------------------------------------------
This is an explaination for stored programs(stored procedures, triggers, packages...):
1. EVERY SQL statement execution may be composed of these steps in a client/server environment:
   (1) The application program submits its SQL statement to the database server via network protocols.
   (2) The database parses the SQL statement, checking for syntax errors.
   (3) The database optmizes the access paths of tables,indexes,
       and then generates the execution plan of the SQL statement.
   (4) The database performs the execution plan.
   (5) The database returns the result set to the client program via network protocols.

2. For batch processing, mass of networking/parsing/optimization/data transmission overheads will be charged
   in a client/server environment if you put your business logic in a client application program.

3. For stored programs, the SQL statements are pre-parsed and stored in the database,
   The only overhead is a remote procedure call via network.
   In some cases, the performance of a stored procedure can be thousands of times better
   than the same logic in a client application program.
   Performance can be a critical issue for batch applications.

Hi Greben,

I'm currently evaluating open source databases and my company is about to make a major shift away from MySQL because our developers have hit a wall of limitations. (btw, I'm appalled that there's no PostgreSQL section here!)

We host our application with 1000+/- MySQL "databases" (AKA schemas) over the Internet. (We have a large, very successful and profitable web app with a substantial customer base with annual user conferences around the world.) There are lots of end user queries peaking at about 200 queries per second for extended periods on some databases with a rare max peak of 800 queries per second. Our developers want to standardize on one open source database for initial coding with secondary support for Oracle and MS SQLServer. Currently our primary database is MySQL/MyISAM with lots of extra coding to work around its non-ANSI compliance, due mostly to the lack of subselects, unions and limited support (or non-production support) for ACID compliant transactions. These are future features for MySQL but we need them now and are skeptical as to when they will be delivered based on MySQLAB's past history of delivering production code on time.

We have looked at MySQL/Innodb but are concerned about 24X7 production support. We need to accommodate database growth without having to bounce the database but this in not possible with InnoDB. If we abandon MySQL we will have to migrate all the data to the new database engine. While MySQL has been good for us up to now, future products and enhancements of existing products forthcoming from our developers will be much more transaction intensive and MySQL table locking problems have become a big production support hosting issue. I've also worked with InnoDB hot backups and deemed it not ready for prime time 24X7 support where any data loss is unacceptable.

I looked as SAP DB. It looks promising but it's not there yet largely because the documentation is abysmally incomplete. The fact that it is robust enough to handle SAP R/3 says a lot but if it's too hard to get it to work for our own custom database app then what's the use? I've gone through several install problem iterations with SAP personnel but have yet to achieve a good working solution.

So that leaves PostgreSQL vs Interbase/Firebird. Postgres seems to be quite mature and robust. Firebird has some very interesting capabilities like row level locking but it's not scheduled to be production until sometime this month. It has some history to it and there are some large site using it but my general hazy impression is that it's not quite as mature as Postgres. It also has more emphasis on Windows and I want something with a strong focus on *nix, full open source infrastructure. I'm still investigating with an open mind on Postgres vs Firebird and I'm looking for a matrix comparing the features and strengths of both as well as some good case studies on real world 24X7 implementations with TONS of data, lots of concurrent multi-user web access and high transaction intensity. Basically I'm looking for the capabilities of Oracle in a free open source database with a minimum of compromises.

Here's some thoughts on MySQL and open source databases in general:
---------------------------
MySQL may be "free" but it can become very costly if you have to engineer around limitations and/or its non-ANSI SQL compliance. The lack of subselects, unions, ACID compliant transactions, etc ad nauseum could be hugely significant. The same is true for other open source databases like SAPDB. Do your applications need a HIGHLY scalable concurrent multi-user OLTP database engine that is always on 24X365.25 (24X7) and does your database have proven, easy, and dependable backup and recovery capabilities? Will your apps ever grow and need a database engine that is robust enough to support this? If you're paying engineer/administrator salaries to work around these limitations of open source databases then it doesn't take too long to eat up and surpass the license costs of a commercial database. Too many people are making database decisions based on very simplistic and shallow criteria such as, "How much does it cost?" and theyre only thinking in simplistic terms of cash outlay. The real cost includes learning curves for your developers and how much work is needed to work around the limitations. If your solution isnt scalable then you will have higher hardware costs. Are there standard DBA practices which you can depend on or do you have to figure it our yourself and hope you didn't miss anything? Can you risk data loss? Can you risk down time?

Some people conclude that MySQL is fast but they only look at it from a simplistic single query perspective-- pretty stupid if you need concurrent multi-user OLTP access where MySQL can actually turn out to be quite slow. The "benchmarks" provided by MySQLAB are shallow and poorly reflect on their sense of what is important. InnoDB can provide some solution but what if you can't take the database down but need to because thats the only way to add storage? SAPDB may handle some of these things better but if it's new, poorly documented, and its future is still uncertain is it worth the risk?

If you have to spend time futzing with this stuff then that detracts from your focus on your own software/service/business solution. I guess some folks like to futz instead of focusing on the business at hand. Some folks think they can do anything and everything and would rather re-invent the wheel via an open source database engine than pay for perfectly good database software and focus attention on the business solution. Its kind of a perverse "not engineered here" and "I can do anything" mentality amongst the open source community. If you standardize on a database with significant limitations then you are starting out by saying, "I will never need scalable, fault tolerant 24X7 access with a guarantee of no data loss. I have no ambition for the business and I dont want to be prepared for successful growth. My web service could never become an amazon.com or I dont care about this stuff because I'm willing to risk everything on the notion that I can do it all myself with free stuff I download off the net." There's a "nerd think" which says it's more fun and technically respectful to futz with open source stuff than it is to focus on a business solution.

So where does that leave me? After all is said and done I still have get beyond the white papers and test things myself. To that end I'll be installing Postgres and Firebird and putting them through the paces with an eye towards production database administration support in an always-on Internet world. It's worth the effort because the biggest investment is not in licensing. By far the biggest investment involves the company commitment to learn, develop on, become proficient in, and provide production support for the chosen database software engine. This may be harder to quantify than the number of dollars spent on licenses but it is much more significant in the long run.

All this having been said, it is quite possible to have a very good business solution which is built on top of an open source database engine but you had better know good and well what youre getting into and know what the limitations and challenges are BEFORE you get started. This in itself could become a major research project. What's tried and true may not work for you. Looking for a "free" open source database engine? Buyer beware... You get what you pay for... one way or another.

If you are only looking for maximum performance in one single-user system(or very few users), the best performance will be in self coded database engine. You can code your db exactly to one purpose. That's not even very hard to do. Many "free" db's are advanced based on that kind of needs.

When you add some features, maintenance aspect, standards(sql) and compatibility then performance usually is not so overpowering anymore even in that one purpose.

There is really good comments why Oracle is better than others and especially better than free db's. I agree most of them. Here is simple practise for testing how db come through concurrence actions:

-create two connections(sessions) to db.
-in first session do:
create table test (a number)
insert into test values (123)
commit

begin transaction
update test set a=234

-hop to another session and do:
select * from test


-Important thing is that you don't do any commits after "begin transaction". Also if there is some autocommit-feature set it off.

You will see in most db's that the second session hangs. There is row-lock and your query is blocked.(MS-Sqlserver, Sybase, DB2, Mysql)

Oracle never blocks select's !  I don't know any other DB products with that feature - it's multiversioning actually.

Well in some db's(Oracle also) you can set different "isolation level" and select is not blocked. BUT then you get wrong answers: uncommited rows.

This is one big reason why Oracle is the best in many environments. Simultaneous updates and especially transactions are really evil in most RDBMS.

My $.02 on open source DBMS's:

I too am disappointed in the lack of attention paid to PostgreSQL, which in some ways is one of the most advanced DBMS systems available anywhere (commercial systems included). While PostgreSQL is still a few features behind Oracle in the enterprise arena, is definitely a contender. MySQL (IMHO) does not really deserve to be directly compared, but unfortunately that is the question everyone seems to be asking. So, with apologies, I will reprint a FAQ I have written on the subject:

---------------------------------------------------
IS THERE A GOOD REASON TO CHOOSE POSTGRESQL OVER MYSQL?

To start with, comparing PostgreSQL and MySQL directly is a serious mistake. The real question is "what kinds of applications do you want to build?". I have had experience building complex web-based applications with both MySQL (mainly against my will) and PostgreSQL, and here are some of the conclusions I reached:

The basic (over)generalization I often hear is: use MySQL for basic dynamic websites, and very lightweight (read: non-critical) applications, but use PostgreSQL for Serious stuff. Well, this is not a bad generalization, but even for lightweight applications, PostgreSQL can really save you some time, and a lot of server-side code, if you bother to learn about views, stored procedures, triggers, etc...

Here are just the basics on what MySQL lacks, which PostgreSQL covers nicely: (I know MySQL is working on some of them, but it is rather late in the game, it seems to me...)

1. Foreign Key Constraints (yes, certain table types now have this, but even this support is
officially still in Beta, and I question just how thoroughly it is being implemented--it is treated as an add-on, rather than the absolutely critical central feature it should be)

2. Views. In the last MySQL application I worked on, THIS was my biggest headache (I later convinced the customer to switch to PostgreSQL). Views provide excellent ways to build one query on another and have logical independence from the physical data. A DMBS should not even be considered remotely 'relational' without views.

3. Subqueries. they seem like such a small extra feature, but there are times when a subquery can save you many lines of application code, extra looping, etc...

4. Data Integrity Constraints such as:
  a. Triggers -- Methods to make sure that if X happens, we also do Y every time. Again, a critical feature if your database contains any serious business logic.
  b. CHECK constraints (don't allow data in a certain table or field unless it fits the parameters YOU decide.)
  c. RULES --PostgreSQL actually provides for methods to rewrite queries on the fly, based on certain conditions. This is a very useful feature, allowing you to catch certain things in ways that triggers don't. For example, a RULE can use the DO INSTEAD syntax so that the requested query doesn't happen at all, while a different query does happen. One example of it's use is to make views updateable, by INSTEAD updating the related tables: a great way to shield programmers from the internals of a data model, in large-scale applications.

5. Stored Procedures -- Is there a complex problem that you would like to have solve IN your database rather than in your application code? Business logic should reside in the database whenever possible, so that application code cannot accidentally circumvent it. For enterprise-level applications, stored procedures are critical. PostgreSQL supports stored procedures in several languages: SQL, PL/PGSQL, Perl, TCL, C, and I believe there are even plans for stored procedures in Java.

6. Full constraints for Date column types -- I had a very difficult time with the last MySQL database I worked on, because the whole database was about time differences in events, and I found that MySQL has several bad habits with time data:
  a. Wrong dates can be entered. For example, one can enter a date of 0000-00-00. This is wrong because no year has a 0 month, and no month has a 0 day. Also, it allows up to 31 days for ANY month, even February, which should only have 28 days. (when you think about it, this means every month can actually have 32 days in MySQL, because it starts at 0).
  b. No truly good methods for measuring date intervals. PostgreSQL was a lifesaver here, because it actually has an "interval" datatype, that shows time intervals in human-readable format, such as "2 Years 5 Months 23 Days 18:03:23". This, combined with PostgreSQL's date calculation and comparison features, make it a great database for handling statistical, event-based data.

7. Everyone is wondering why I left out Transactions. I left transactions to the end, because too many people focus on transactions as the only important feature when discussing these things. No, transactions are just a basic underpinning. Transactions can't prevent you from corrupting your data, they can just prevent it from being caused by crashes or other anomalies. Yes, MySQL has transactions now, for two table types. Big deal. Transactions are should be a given, not even a point of discussion.

---------------------------

These things I mention above are just the basics for a true relational database. This is before you even think about such things as scaleability. Scaleability is pointless if your data is bad, or unmanageable. RDBMS stands for "Relational DataBase Management System". Maybe MySQL should only be called a "DBS", leaving out the "Relational" and the "Management" parts.

In terms of scaleability, PostgreSQL again wins out over MySQL. MySQL may seem blazingly fast for single use access, or for mainly read-only access, with few inserts or updates, but there are many ways to optimize. PostgreSQL is optimized for serious work, where there are many concurrent updates, inserts, etc... with many users. PostgreSQL seems slower than MySQL in doing a single query, but when the number of users scales and the complexity of the queries goes up, PostgreSQL just keeps on handling it.

Also, for those who really want to delve into the theoretical end of things, PostgreSQL has a few cool extras:
1. Specialized datatypes-- there are several special datatypes aimed at IT and science/engineering, such as network address datatypes, and spatial datatypes (with special algorithms for sorting 3D data)
2. User-defined datatypes. Yes, you can create your own datatypes that behave by your own rules.
3. Object-relational inheritance. This is not to suggest that PostgreSQL is trying to become an "O-O" (non-relational) database, but that it does allow tables to be created which inherit base attributes from parent tables, sharing the inherited columns back "downward" to the parent. This allows for some interesting database design methods.

Another important area of concern is reporting.  I have found out through some more painful head-banging operations that if you want to do real reporting, a real database helps. PostgreSQL supports JDBC and ODBC, which are the two main interfaces required by almost all reporting tools, which leads to:

1. Most serious reporting tools need to know foreign key relationships, in order to assist the user in building decent reports.

2. Views, views, views: A few views, a nested subquery or two, and suddenly a unmanageable data becomes crystal clear. The greatest thing is you get to build views on top of other views, rather than create Ye Massive Multiple Join Query of Death, as with MySQL.

3. Stored procedures, or functions can act like customized "widgets", where you can just pass values or recordsets as arguments, and have complex operations performed.

---------------- Any good points to MySQL? --------

Yes, let's be fair:

1. MySQL is easier to deploy in certain situations, however even that has been changing lately with PostgreSQL.

2. MySQL runs natively on Windows, giving many developers an easy workstation to develop their apps before deploying to a Unix or Windows server. PostgreSQL is still primarily a Unix DBMS, although it can be installed on Windows with the Cygwin kit. (there is a native Windows port under development)

3. MySQL supports an easier user-management system. MySQL defines all user permissions right in the database, without any reliance on Unix authentication (some see this as a bug, not a feature ). MySQL also supports more fine-grained approaches to these privileges, so user access can be defined on databases, tables, and even columns. PostgreSQL does not yet have as comprehensive a method for managing users, although it is approaching that level quickly.

4. MySQL has an official replication and hot backup system. PostgreSQL has several projects which are at various levels of completion, but not an official release, such as MySQL's. UPDATE: There are serious data integrity considerations in data replication which I have not explored here. The PostgreSQL team is working on an enterprise-level replication package which I believe addresses these issues. (http://gborg.postgresql.org/project/pgreplication/projdisplay.php)

5. MySQL has some speed enhancements for read-intensive databases such as dynamic websites, which promise to make certain types of websites perform very well.

6. MySQL's development is moving at a fast pace, and they claim they intend to support all standard ANSI SQL features, eventually. Let's wish them the best. Meanwhile, just read comp.databases.postgresql.hackers and you will see that PostgreSQL is definitely keeping good time.

------------------------

Search www.phpbuilder.com for some very interesting articles about PostgreSQL, from the main developer of SourceForge, who first tried to do it with MySQL, but was very surprised to find that PostgreSQL handled the job much better.

And if you are concerned about support, PostgreSQL has every bit as much support as MySQL. There are newsgroups, mailing lists, forums, and even paid support levels, (see http://www.pgsql.com/support).

Also, for a serious website about understanding exactly what a relational database management system is, and why this is important, visit www.dbdebunk.com. The contributors are a couple of the most respected authors in database design and theory. C.J.Date is the senior contributor, and he also happens to be one of THE original researchers that helped develop the relational database concept in the 70s)

rycamor: "PostgreSQL can really save you some time, and a lot of server-side code, if you bother to learn about views, stored procedures, triggers"

Well - I've been studying the different DBMS's and found out that maybe I should start using PostgreSQL since it (sounds like that it) is almost as good as Oracle (and it's free)..

But rycamor? Where is there a good tutorial learning about views, stored procedures, triggers and stuff like that?
I really until now haven't worked with stuff like that - I've only made some PHP/ASP-scripting on MySQL (and unfortunately Access (only because my school didn't care about that we (the pupils) wanted something better)), which means that I've only worked with simple queries where the most difficult was to integrate three-four inner joins in a single query!!! Yeah - maybe it's nothing to you - but I really wanna learn this stuff and maybe make it my future way living!! So I need to learn this stuff!!! I really would like to!!
Maybe it would be nice having a guide to tell me precisely about the transaction and commit definition! I'm aware of what the basics about it means, but not about how it works and when/where I should use it :)

Really hope you can help - the last couple of days I've really learn a lot of theory about DBMS's!!

Thanks!!!

Well, if you are interested in PostgreSQL, then the best place to start is http://techdocs.postgresql.org/
There you will find at least two online books about PostgreSQL, which give examples of all major features, using standard SQL. Also, you can visit www.postgresql.org/idocs , which contains the standard user documentation. Subscribe to a couple of the mailing lists, and you will find ample good material.

A few other good links:

http://www.dbforums.com/
http://forums.devshed.com/
http://searchdatabase.techtarget.com/
http://developer.mimer.com/validator/index.htm (validates your SQL syntax)
http://www.datamodel.org/
comp.databases.theory (excellent newsgroup)

Good books:
- "Database Design for Mere Mortals", by Hernandez (a beginner's book, but contains some good material)
- "An Introduction to Database Systems" by C.J. Date (a classic, now in 7th edition)
- "A Guide to the SQL Standard" by C.J. Date
(really, C.J. Date is probably THE definitive writer about databases. Some of his later books are much heavier in theory, such as "Third Manifesto", but IMHO this is one of the best minds in the business)
- "Practical Issues in Database Management" by Fabian Pascal, one of C.J. Date's associates.
- "PostgreSQL Developer's Handbook" by Ewald Geschwinde and Hans-Juergen Schoenig (a good book on PostgreSQL, although a little short on database fundamentals)

Also, remember: in the commercial world (and to some extent even the open source world), much of what you read about DBMS's is "covered in marketing drool" (one of my favorite quotes from the PostgreSQL developers mailling list). If you ever read about some particular technology being, "Post-relational", "XML-integrated", "Multidimensional", "Multivalued", "Application-integrated", "Object-oriented" etc... then chances are it is not really a new thing at all, but a throwback to the pre-relational database systems of the past, which were either heirarchical, or network-oriented, and which caused so many problems that the relational data model was discovered to solve.

I mentioned the www.dbdebunk.com website in my first post, because, although sometimes the theory is pretty dense there, at least those guys have the courage to call a spade a spade. Their whole point is to debunk the popular  misleading claims about database technology. Generally, you should take 95% of what you read in the popular tech/IT press about databases with a grain of salt. Most tech reporters haven't the slightest clue what a database is, much less the theoretical foundations, so they just regurgitate the advertising claims. Study fundamentals first, and leave all the XML-integration and database-turned-application-server nonsense for later. (Most of it really is nonsense)

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)