A database is a program that stores data and answers questions about it, safely, while several things use it at once. That last part is why you use one instead of a file.

Tables, rows, columns

A table holds one kind of thing — users, orders, notes. Each row is one of them; each column is a fact about it. Every row needs an ID that uniquely identifies it.

Relationships are built by storing one table's ID in another table. An order stores a user_id. That's a foreign key, and it's the whole idea.

The four operations

SELECT to read, INSERT to add, UPDATE to change, DELETE to remove. Almost everything is a variation.

The one thing worth burning into your memory: never build a query by concatenating strings with user input. Use parameters. This is the most exploited vulnerability on the web, and the fix is a small syntax change.

Indexes

A query without an index reads every row. With one, it jumps straight there — the difference between scanning a book and using its index.

Index the columns you filter and sort by. Don't index everything: each index makes writes slower and takes space. Start with the columns in your WHERE and ORDER BY clauses.

This is invisible at 100 rows and dominant at 100,000. It's the most common reason a working app gets slow.

Transactions

A group of operations that all succeed or all fail. Moving money between accounts is the classic example — you must not deduct without adding.

Any time two changes must both happen or neither should, wrap them in a transaction.

SQL or not?

Use a relational database — Postgres, MySQL, SQLite — unless you have a specific reason not to. They're mature, well understood, and handle far more than people assume. SQLite in particular is a single file with no server and is genuinely sufficient for many real applications.

Document databases suit genuinely unstructured data. Most projects that choose one would have been better served by a relational database with a JSON column.

db-review.txt
Here's my schema and my most common queries: <paste>

Review:
- Which queries lack an index they need? Show me the index.
- Any string-concatenated query? Show me the parameterised version.
- Any group of writes that should be in a transaction and isn't?
- Any column that should be NOT NULL or unique and isn't?
- What breaks first as this grows, and at roughly what size?

Learn to read your database's query plan. It tells you exactly whether an index is being used, which turns performance work from guessing into reading.