Picking a database

Tables and rows

A relational database, like PostgreSQL or MySQL, keeps data in tables. Each row is one record, like one user. Each column is one field, like email. The schema lists the columns and their types, and the database rejects data that breaks it. A primary key, like an id, marks each row. You ask questions in a language called SQL.

Joins

An order row holds a user id, which points to a row in the users table. That pointer is a foreign key. A join puts matching rows together, like each order with its user's name. Because tables can be joined, each fact is stored once.

Transactions

A transaction groups changes so they all happen or none do. Moving $50 from Alice to Bob is two changes; if the server crashes between them, the database rolls back. Its promises are called ACID:

• Atomic: all or nothing.
• Consistent: the data's rules always hold.
• Isolated: others don't see half-done work.
• Durable: once saved (committed), it survives a crash.

Kinds of NoSQL

NoSQL databases don't use SQL tables. Four kinds are common:

• Key-value: get a value by its key, fast; good for sessions.
• Document: records whose fields can differ; good for mixed products.
• Wide-column: huge write volumes over many machines; good for sensor readings.
• Graph: things and the links between them; good for friends of friends.

To spread data over many machines easily, many give up joins, and often transactions across many records.

Choosing a database

Strict structure, links between data, or money: choose SQL. Huge scale with simple lookups, or data whose shape keeps changing: consider NoSQL. When unsure, start with SQL; it serves most apps for a long time.

Remember

• The schema sets the columns; the primary key marks each row.
• A join puts rows together by key, so each fact is stored once.
• A transaction is all or nothing.
• Match the NoSQL kind to the shape of the data.
• Start with SQL unless you have a clear reason not to.