Database Fundamentals

The mental model: what a database is, why we use one, and how the pieces fit.

Concept guideUpdated 21 days ago (29 Sept 2026) by Developer B

Download .md Done editing

Title

Type

What it is, why we use it, the architecture and real examples. The material behind a Concept Sharing session.

Summary

Body

# Database Fundamentals

The material behind the concept sharing session. The goal is the correct mental
model, not every implementation detail.

## What is a database?

A database is a program whose job is to store data safely and answer questions
about it quickly. We use one instead of files because it gives us three things
files do not:

- **Structure** — the shape of the data is declared, so bad data is rejected.
- **Concurrency** — many people can read and write at once without corruption.
- **Querying** — we ask *what* we want, not *how* to find it.

## Tables, rows, columns

A table holds rows of the same shape. Each column has a type and may allow NULL.

| Column | Type | Null | Meaning |
| --- | --- | --- | --- |
| id | text | no | Identity of the row |
| email | text | no | Unique per member |
| title | text | yes | Not everyone has one |

> NULL is not zero and not an empty string. It means "we do not know". That
> distinction matters in every comparison you write.

## Keys and relationships

- **Primary key** — identifies a row. One per table.
- **Foreign key** — points at another table's primary key. This is what makes
  the data relational.
- **One-to-many** — a skill has many checklist items.
- **Many-to-many** — an assessment cites many evidence items, and a piece of
  evidence can back many assessments. That needs a join table.

## SQL in one minute

```sql
SELECT m.name, s.name AS skill, ms.verified_level
FROM member_skill ms
JOIN member m ON m.id = ms.member_id
JOIN skill  s ON s.id = ms.skill_id
WHERE ms.verified_level < ms.target_level
ORDER BY m.name;
```

Read it in this order: `FROM` → `JOIN` → `WHERE` → `SELECT` → `ORDER BY`.
That is roughly what the engine does too.

## Common problems in our projects

1. **A missing index on a foreign key.** Joins get slow as the table grows.
2. **An UPDATE or DELETE without a WHERE clause.** Always run the SELECT first.
3. **Doing work in a loop that SQL could do in one statement.**
4. **Long transactions** holding locks while waiting on something external.

## What to look at next

- The checklist on this skill, in order.
- Our own schema: `prisma/schema.prisma`.
- *Use The Index, Luke* for indexing.

Markdown with tables, code blocks and task lists.