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.

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

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.