Relational databases for structured data
SQL databases enforce a schema: every row in a table has the same columns with consistent types. Joins let you query across tables, and ACID transactions guarantee consistency even during failures. This works well for financial records, inventory, and user accounts where structure is stable. The cost is upfront schema design and migration pain when columns change.
Document and key-value stores for flexibility
Document databases (MongoDB, CouchDB) store semi-structured JSON/BSON with flexible schemas. You can store different fields in different documents and add fields without migrations. Key-value stores (Redis, DynamoDB) offer no schema at all, just opaque values keyed by strings. Both are fast for retrieval by key but lack the rich query power of SQL. Joins are done in application code, slower and error-prone.
Specialized stores for specific patterns
Graph databases excel at relationships and traversals (social networks, recommendation engines). Time-series databases compress metric streams efficiently (monitoring, stock ticks). Wide-column stores (HBase, Cassandra) handle massive sparse matrices for analytics. Picking the right store means matching your access pattern: if you do complex joins, go relational. If you read by ID and append-only, key-value is simpler.