I need to learn some basic methods for updating a database with online forms.
What is the best database to use? Recommendations?
Also, what is the market like for database administration and development for websites?
Thanks!
All relational databases operate on the same fundamental principals. Some have more features than others, and some are free and some cost money. Which one you use depends on what kind of application you’re creating. MySQL is easy to set up and learn, (and it’s free) so it’s a good starting point for learning relational data theory. The important thing is learning the concepts behind how relational databases work. Once you understand that, you will be able to use any RDBMS with very little effort.
I reccomend this book, as it gives a superb overview of relational theory and then has instructions on how to use several different SQL-based databases.
If you learn SQL, you’ll be able to switch to any SQL-based database pretty quickly. MySQL is missing a few features that the more expensive databases have, but I’ve never had a need for them myself.
MySQL is a fantastic database. We use it for some very large commercial applications.
If you want to be a database administrator, it won’t do a lot for you, because where it lack is in the really heavy duty, complex stuff like replication and synchronization and all that.
But if you want to program databases, it’s perfect. It’s free, it’s easy to install and get running, and it’s a snap to use. And if you’re doing web form stuff, the actual database engine is abstracted away anyway by ODBC or ADO or whatever technology you’re using.
What you want to learn is SQL. MySQL is a fine implementation, and will do everything you need. Some very large commercial websites use MySQL.
MySQL Home Page. You can download it from there.
They are basically two separate market… If you want to make your living off database administration, go learn Oracle and you can get a nice comfy job in a large corporation optimizing SELECT, data arrangement, network connectivity, backing up databases, etc.
But if you want to do website, then the database is only a peripheral dependency to your development, usually set up by someone else in the production environment.
In that case, choose your development language and get the easiest db setup you can. PHP/MySQL is a nice trio for this, simple and easy to learn - its also included with Linux distribution such as RH or you can use a Windows distribution too such as http://www.easyphp.org/?lang=en.
To expand on the administration market, the way I’ve been seeing things is that larger companies use Oracle, small companies are more likely to be using Microsoft SQL Server, financial and telecom companies have a large Sybase userbase, and companies with a lot of mainframes might use IBM’s DB2.
These are all relational databases, so the underlying theory and SQL syntax is pretty standard. The actual administrative work (setting up servers, backing up databases, etc) is going to be very different. So unless you’re actually looking at being a database administrator, just learn SQL, table key definitions, etc on whatever’s handy. You’ll be able to deal with different DB types’ idiosyncrasies as you come across them.
In my experience, you can almost always find any of the big databases in any reasonably sized corporation. I’ve worked in “exclusive Oracle” shops that also supported a couple of SQLServer databases, and “exclusive IBM shops” that had Oracle or Sybase installs hanging around. Learn basic SQL anywhere and then pick up the administration for one or two of the big guys (Oracle, SQL Server, Sybase, DB2) and the free/open ones (MySQL, postgreSQL, etc.) are always great for learning and in some business applications.
To amplify on the “all SQL is basically the same point”, you will find differences, particularly in join syntax. However, most systems are slowly converging to the SQL-99 standard. With both Oracle 9i and SQLServer 2K, you can actually use the “LEFT OUTER JOIN ON tablename.columnanme = tablename.columname” syntax, instead of the more cumbersome (+)= junk that was different for each system.
On the other hand, stored procedure languages are different for each system, and likely to remain so. As another point, make sure you pick up a programming language or two. Oracle, for instance, can run Java programs inside stored procedures, and SQLServer will be able to run any .NET language (C#, VB.NET, etc) in a stored proc in the not too distant future.
If you want to learn web development, here’s an excellent, free solution:
-
Go download Eclipse. It is a free Java IDE. That, coupled with Sun’s JDK1.4, will get you the latest development setup. This setup is so good that my development team is in the process of dumping our $2500+ JBuilder Enterprise IDEs in favor of Eclipse.
-
Get mySQL, and install it.
-
Download Apache, and Tomcat, and install them. Tomcat is the reference servlet and JSP container, so you can build enterprise-capable, database driven web sites with it. The newer versions even perform well.
-
Alternatively, if you want to learn J2EE, get Jboss, a free J2EE implementation.
There’s no need any more to spend a nickel on commercial database systems or commercial development tools. This stuff is all excellent quality, and you can write applications of any complexity you require. You can learn Javabeans, servlets, JSP programming, SQL, SOAP, and all the other current internet buzzwords. This ‘technology stack’ is already in use in large companies like IBM and GE.
One other thing - actually just learning to build database tables and get data in and out of them is almost trivially easy - which is why there are so many godawful databases around. Take the time to learn how to structure a database properly, with proper normalization, etc. If you want to become a pro, you don’t just need to be able to do it, but to do it right.
OK - I’m gonna get murdered for this but here goes…
Learn all about the databases previously mentioned, but learn MS Access very well also.
I know many people think of it as a toy (and not without some good reasons) but the fact remains that if you work for a large corporation, this will be one of the only database tools your clients will have on their desktops.
It may not be the best (or even in the same league), but if it’s all your customers have, you’re gonna fine yourself working with it. So learn it.
I thought MySql has some limitations regarding multi-user applications? Or has that been changed in the more recent versions?
A few years ago Oracle DBA’s were in high demand and were highly paid. I knew an Oracle DBA that got a job offer over the phone with no interview. I guess with the economy going downhill things are not quite that good now.
I second the recommendation for Access, (but as zoid says, not as a primary tool) - it is great for ‘quick and dirty’ stuff, especially for stuff like like data transformation that can only be done by iterative updates, rather than update queries.
I love Access. I use it as no on in my company can use email so I can set up a switchboard and sit back and relax.
Also as a report writer I find it easier to use than Crystal.
As others have said, if you’re looking for job prospects learn Oracle and SQL server (add DB2 if you really want to work in a mainframe environmen shudder)
If you want to do web development, anything will work for the basics so go with the freeware first. Eventually, though, you are likely to hit the same business environments as everybody else, and to be really good you will have to know the tricks to optimize queries against the databases they use. So learn SQL server and Oracle. (BTW, Oracle is really pushing hard to capture the emerging Linux marketshare, too. I wouldn’t bet against them at this point, though it’s too early to call.)
I hadn’t heard anything about that, (MySQL has a fairly robust user and permission system) but one of the common complaints is that MySQL does not have support for proper foreign keys and stored procedures. Still, I find it’s adequate for most small-to-medium size applications.
I think the newer versions of MySQL do support foreign keys (and transactions?) if you use InnoDB.