Types of database

Not every database stores data in tables. Each type shapes data in its own way, and that shape decides what it is good at. Here is a quick map:

Don’t try to learn all of these now. Trying to remember every type at once will go over your head and leave you with nothing. You only need two families to start: SQL databases (relational) and NoSQL databases (key-value, document and the other types in the table). The rest will make sense later, once these two are clear.

7 database types at once

Went right over my head

SQL databases

A SQL database, also called a relational database, keeps data in tables with a fixed set of columns, called the schema. Every row must follow it. You read and change the data with SQL.

Tables link to each other through keys. Take a simplified Instagram: one table for users, one for posts and one for likes. Each post stores the user_id of who posted it, and each like stores which post was liked and which user liked it. Columns with the same colour hold the same kind of key:

Good for: data with a clear structure and many relationships, such as orders, payments and stock.

Examples:

NoSQL databases

NoSQL means “not only SQL”: any database that does not store data in tables of rows and columns. The simplest kind is the key-value database.

A key-value database works like a giant dictionary. You store a value under a unique key, and you get it back by asking for that key. There are no tables, no columns and no schema: the value can be a number, some text or a whole object. Back to our Instagram, here is what a key-value database might hold:

session:8f2a      →  { "user_id": 1 }
likes:post:101    →  2
user:1:username   →  "asha.codes"

• session:8f2a remembers who is logged in, so Instagram does not ask for your password on every tap.
• likes:post:101 keeps the like count for a post, ready to show instantly.
• user:1:username stores one detail for quick lookup.

The catch: you can only find a value by its exact key. You cannot ask “which posts have more than 100 likes?” the way you can with SQL. In return, reading or writing by key is extremely fast, often under a millisecond, because many key-value databases keep their data in memory.

Good for: logins and sessions, caches that keep popular data ready, counters such as likes and views, and shopping carts: anything you look up by one known name.

Examples: Redis, Amazon DynamoDB, Memcached.

Which one should you use?

Here is which one fits common jobs in an app like Instagram:

If you are not sure, start with SQL. Most apps begin with a SQL database, and it teaches you the ideas every other database builds on. Many apps then add a key-value database next to it, for speed.

Words to know