Design - continued .....

SQL Database Server

The most obvious choice for data storage within a Web based system is a networked SQL database server, which is what I chose. At present the two most popular Open Source Database Management Systems are MySQL and PostgreSQL, both of which are more than adequate for this project’s data storage requirements. I shall attempt to compare these to DBMS’ and decide on one or the other as the method of data storage for this project.

MySQL vs. PostgreSQL

To begin our comparison, it is claimed that “The MySQL database server is the world’s most popular open source database.”, which may be true , but that does not translate into being the ‘best’. A second description provided by MySQL.com of the MySQL DBMS is that it “delivers a very fast, multi-threaded, multi-user, and robust SQL database server”. While PostgreSQL.org claims that the PostgreSQL DBMS has a larger feature set, comparable performance and similar reliability to any other DBMS.

Clearly claims made by the development bodies contradict somewhat as they obviously aim to glorify their own product. As such independent, non-bias opinions were sought, of which two freely available comparisons of the two systems were found. The first article I looked at claims out right that “it [MySQL] should never be used by any system that handles critical data.”, some of the article’s reasoning is outlined below.

• MySQL claims that they have compromised on certain features to guarantee better performance . . . compromising on perfect data integrity is not acceptable, even for speed, when dealing with critical data.”
• “MySQL has no subqueries.”
• “MySQL has no stored procedures.”
• “MySQL has no triggers or foreign key constraints.”
• “MySQL only has table-level locking. ”
• “MySQL is just a glorified file system with a SQL interface.”

The second article that I read sought to “grab a ‘real life’ web page from a ‘real’ web site and see how it performed on the two databases”. Seeing as I will be using the DBMS with a Web system this would seem an ideal comparison. After many tests were performed the article offered little in terms of a conclusion except to state that “for the hardest-core developers, Postgres could be pretty slick” in the sense that it has a much more complete feature set and “PostgreSQL withstands up to 3 times the load that MySQL can before throwing any errors”, the author of the article does however point out that if you do not need to use the extra features that PostgreSQL can offer “then you’re probably better off with MySQL and its superior performance.”

Based on this evidence, the choice would be PostgreSQL as the DBMS of choice for usage in this project, especially when scalability is taken into consideration. If the system was integrated with the existing system, which is a often under very heavy load as many users require simultaneous access, then usage of MySQL as the DBMS may prove catastrophic when the system is in heavy use and data loss may occur.