When people ask me how to learn to use a database or how to write SQL queries, I tell them to pick a database system and immerse themselves in it. In fact that advice goes for a lot of software technologies: just immerse yourself in a language, as programming tutorials are easy to come by these days. On the other hand, when people ask me which database software to use, I tend to give pause. Most of the time, I recommend MySQL for beginners since it tends to be the most light-weight system to install and use, but I know it’s not often the easiest to understand. With the advent of new light-weight database editions of often heavier products, perhaps it’s time I reconsider the issue.
1. MySQL: Free, lightweight, and readily available
MySQL stands out as the easiest for users to start with, in part because most people can get access to a MySQL database without having to setup anything. Most, if not all, hosting companies that offer database support do so in the form of a MySQL database. The only disadvantage with hosting solutions is that users lose the ability to run local applications on the database, often relying on phpMyAdmin for all database changes. I recommend anyone serious about learning MySQL download and install it themselves, as there are plenty of installation platforms supported.
The good: Free. Easy to download and/or find an existing database to work with. Somewhat easy to install. Lots of free tools available. Good documentation.
The bad: If the installation or auto-configuration breaks, user is left spending hours diagnosing the problems. The MySQL GUI tools, while nice, have to be downloaded separately from the server. Limited support. Clustering and support of large transaction systems is not uncommon. Also, it can be buggy and unpredictable at times, as I’ve seen in practice.
2. Oracle: Heavy and Powerful
Oracle is one of the oldest database systems and stands out as a powerhouse among databases given its vast support for advanced clustering, memory management, and query optimization. If you need something robust, powerful, and able to support millions or billions of transactions a day, it’s the best there is. Oracle needs to be licensed for a production environment, although developers can download a free limited-use version which is good for building an application.
The good: Powerful. Can do some really cool things for those that appreciate it. Extremely scalable.
The bad: Often large and time-consuming installation. Least user friendly of all the database systems, although it’s gotten better over the last few years. Not free. Not a wide variety of tools, free or otherwise, to manipulate the database.
3. Microsoft SQL Server: Easy to use administration interface, often powerful
Microsoft SQL Server has matured greatly over the last 10 years into a decent rival of Oracle. I like MS SQL Server in that it hides a lot of the underlying configuration information from the user. On the other hand, I dislike MS SQL server in that it hides a lot of the underlying configuration information from the user. Double-edged sword, I know. Like Oracle, you need a license if you want to use it in a production environment.
The good: Easy to set up new databases and administer them. Best for those who have no idea how to administer a database. New express editions can be used for free.
The bad: Over-simplifies a lot for advanced users, making it harder to optimize. Not free. Developer edition has nominal cost, although it probably should be free.
Other Databases
This article is not meant to be the end-all for database software discussion, but a beginning guide of the big three database systems for those who are not well-versed in the area. To cover every possible database software, such as PostgreSQL or DB2, as well as countless others, would take a book or two. Most students starting out just need to find a single database and start ‘playing’ with it until they get the hang of it, rather than an exhaustive discussion of which database is best.
Non-standard Databases
Some of you may be more familiar with embedded databases such HSQLDB, SQLite, or Derby than the ones I have mentioned. Rarely do I see beginners using embedded databases, so perhaps I’ll write an article about such systems down the road. Also, I have not purposely not mentioned Microsoft Access as a learning database, simply because I don’t consider it standard database software, but rather a glorified Excel spreadsheet. Most of teaching someone how to use a regular database after using Access, is convincing them all databases are not like Access.
My favorite database? If I’m teaching or writing a relatively simple web-application, MySQL. If someone else is paying for the license and the application is large enough, Oracle.
I would go for MySQL to start with. Also I would like to say that you can use MySQL even if your application is beyond simple.
What is the reason to describe MySQL but not PostgreSQL? Or Oracle but not DB2? That makes the article very subjective without any real arguments why those three are at the top of all database pyramid. For me it looks like that is only traditions that makes those three so popular. And others will never become more popular while there are so many people not seeing other possibilities only those three. DB2 Express is free for a long time which is light and very very fast. PostgreSQL is easy to install and easy to use. It has more scripting possibilities than any other free database.
For me this article looks like the article for popularity as the title would catch lots of googlers.
See my section on “Other databases”. I understand some people are fans of certain databases, but one could spend years discussing all of them. As for DB2, even though I’ve used DB2 a number of times I haven’t heard anyone promote it in a long, long time.
If you are literally starting from scratch, you should consider Apache Derby, which is even simpler to install than MySQL. And if you use Eclipse you can add plugins to support it.
As for DB2, the larger the installation, the more likely you are to encounter it.
In Oracle exist the express edition too, with limit to 4 GB. Exist a tool, SQL Developer and is free. In comercial version include power tools to manipulate the database.
I thought I saw plenty of tools for Oracle myself, but granted, not many free ones. My coworker raves about SQL Developer.
I wouldn’t recommend MS SQL Server to start with as there are too many non-standard syntax conventions.
PostgreSQL has probably the most compliant syntax out there and would be great for a beginner. They would have the least amount of trouble getting something working from any ordinary SQL book.
Use PostreSQL. You won’t be sorry. It’s more a full featured DB.
I found PostgreSQL to be just as easy to install locally as mySql.
Don’t get me started on Derby, I had the misfortune of using it on a J2EE project I inheritted. Most unstable DBMS I’ve ever used, and I’ve used them all.
This is a bit arbitrary I think. For a start, in Oracle-land, there is the free Oracle XE. That one’s saved my butt a few times when I needed to write “proper” Oracle PL/SQL for things like procedures that will run correct and exactly the same on a big installation. So Oracle XE is very powerful and fully featured except for it’s maximum allowed database size (4GB) and users (5, I think).
In other arenas big IBM shops will often have DB2, it’s very powerful.
I can also vouch for PostgreSQL – of the fully free databases it’s my pick over MySQL, which in my experience tends to be non-standard in things like connection protocols, and in the confusing array of underlying database file types, which influence what features you get. PostGreSQL feels more like a “real” database, it’s powerful and fast and is free and I highly recommend it for people needing a powerful but free db alternative.
Derby is OK, HSQL OK, but mostly for the purposes of testing and development.
Microsoft SQL Server Express, free.
couple of years are I’d go with PostgreSQL, but now that DB2 has Express-C edition that is free, I’d go with it. I’d use it for small projects where database does not use more than 2cores and 2GB or RAM. For a project that would get funding I’d go with other DB2 editions, depending on the funding. 🙂
I prefer..
1. PostgreSQL
2. MySQL
3. MS SQL – Express
I am a beginner and I’ve tried with hsqldb, mysql and oracle.
MySql I thought was supposed to be a simple db, and there were lots of tutorial-code on net, where MySql was used. But then, I got bugged with its configuration diagnosis !!! Previous configurations remained intact even when I had updated them !!!
Oracle, though installation took a lot of time, was very clean, and easy to use !
Hsqldb was simple and easy. But I dint find good tools to use along with it. Maybe I din’t look much.
Nice post !