Now that we have seen some of the benefits and pitfalls of using a database to store your data, we can start to look at the types of spatial databases that we can consider. The first step to consider is, do you want to use a SQL database or a NOSQL database?
SQL stands for Structured Query Language and is an international standard defining a language for querying relational databases. While there are issues between implementations of SQL between different vendors, mostly over time and date values, SQL has proved very successful. By using SQL, external programs are able to abstract many of the issues of accessing databases and allow the user to chose the database they prefer.
To provide the same sort of standardisation for spatial databases, the OGC formalised the Simple Feature for SQL specification. This specification defines the Well Known Text (WKT) and Well Known Binary (WKB) formats for storing spatial features as well as functions for importing and exporting features from/to WKT and WKB. It also defines a set of functions for manipulating and querying geometries.
A free and open source extension to the popular PostgreSQL database. It provides full simple feature for SQL support. The latest version is available from http://postgis.refractions.net/. PostGIS is the DB of choice for research projects in the Department of Geography at Penn State due to its excellent feature set, high reliability, and free licensing. PostGIS is also the spatial backend for Esri's current ArcGIS Server offering on Amazon's cloud (probably due to the free licensing).
SQL Server (Microsoft)
SQL Server is Microsoft's primier database offering. It is one of the "big three" commercial DBMS along with Oracle and DB2. It often provides the back-end for large Esri installations. It gained spatial support back in 2007 (!). Home page: http://www.microsoft.com/sqlserver/en/us/default.aspx.
Oracle Spatial & Oracle Locator
Spatial and Locator are the spatial extensions to the popular, powerful, and expensive Oracle database. They provide simple feature for SQL compliance for types and features but not import and export of WKT or WKB. See http://www.oracle.com/technology/products/spatial/index.html for more details. Oracle was first out there with good spatial support, but it seems to me like the rest have caught up.
IBM DB2 Spatial Extender
The DB2 Spatial Extender allows you to store, manage and analyse spatial data in IBM's DB2 database system. The spatial extender implements the OGC simple feature specification. See http://www-01.ibm.com/software/data/spatial/db2spatial/ for details.
SpatiaLite is a spatial database based on the popular lightweight SQLite package, a self-contained serverless transactional SQL database engine. SQLite is opensource (public domain) software, which has helped its uptake. SpatiaLite is compliant with the OGC simple feature specification (except for renaming union to gunion - due to a reserved word clash). Some commentators have predicted that SpatiaLite may finally replace the Shapefile as a data exchange format.
MySQL claims to be the world's most popular database, but its spatial extensions (http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html) are only a subset of the Simple Feature specification. All of the functions that calculate relations between geometries are implemented using bounding boxes not the actual geometries. This may be acceptable to your application or not, only you can decide.
Other Databases (NoSQL)
In recent years, there has been a trend to using so called “NoSQL” databases. This is a loosely related set of databases that are non-relational, distributed systems that often don't provide an ACID guarantee. ACID stands for atomicity, consistency, isolation and durability which are all great things to have when you are considering a banking system (you'd hate for money to disappear from your account but not arrive at the payee's account) but are considered unnecessary for some modern applications such as FaceBook comments.
There are, as yet, no full spatial extensions for these databases, but a number provide a variant on the geohash solution. Geohashing is a way of converting a spatial location into a single text key (see http://geohashing.org and http://code.google.com/apis/maps/articles/geospatial.html). The other solution adopted by some of these systems is to use a spatial SQL database as a back end for the spatial queries.
Currently, none of the systems I have found seem to be able to handle anything more complex than a bounding box or proximity search. So while they currently seem to be useful only for very limited applications, that may change in the future.