The shape of a database

Every query you will ever write rests on one mental model. Once you have it, SQL stops being a magic incantation you copy from a developer and becomes something you can reason about. This lesson builds that model, and it is the only lesson in the course with more reading than typing.

Tables, rows, and columns

A relational database stores data in tables. A table is a grid:

• a column is a field, with a name and a type, the same for every entry
• a row is one entry, one thing: one post, one category, one tag

Our blog has four tables. Look at categories, the simplest one:

Three rows, four columns. A spreadsheet with rules, and the rules are the interesting part.

The primary key

Look at the id column. It is the primary key: the value that identifies a row uniquely and never repeats within the table. Two categories can never share an id.

In this schema the primary keys are SERIAL, which means the database assigns the next number itself whenever a row is inserted. You do not choose it, and you cannot rely on it being sequential after deletions, but within a table it is guaranteed unique.

👨‍🏫 As a tester, the primary key is your row's fingerprint (🫆). When you find a bad row, note its id. That is the one piece of information that stays true no matter how the record is later edited, and it is what turns "one of the posts is wrong" into "post 14 is wrong", which is something a developer can act on.

The foreign key, and the one-to-many shape

Now open posts:

\d posts

Among its columns is category_id, and at the bottom of the output you see this:

Foreign-key constraints:
    "posts_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL

A foreign key is a column that points at the primary key of another table. posts.category_id holds the id of a row in categories. That is how the two tables are connected, and it gives us a one-to-many relationship: one category has many posts, one post has at most one category.

The foreign key is also a rule the database enforces. You cannot store category_id = 999 if no category 999 exists; the database rejects the row. That is a guarantee about your data that no amount of application code can be trusted to provide on its own, and it is the reason a tester should read the schema before writing the first test.

Two details in that constraint matter later:

• category_id is nullable. A post is allowed to have no category at all. That single fact is behind a whole family of testing mistakes, and it gets a lesson of its own.
• ON DELETE SET NULL. If a category is deleted, its posts survive, with category_id set to NULL. The database decided what happens, not the application.

The join table, and the many-to-many shape

A post can have several tags, and a tag can be on several posts. That is many-to-many, and there is no way to express it with a column on either side. Which column would hold three values?

So a relational schema uses a fourth table, post_tags:

\d post_tags

Two columns, both foreign keys, and together they form the primary key. Each row means "this post has this tag". A post with three tags produces three rows here. That is all a join table is: a list of pairs.

Two consequences worth carrying with you:

• Because (post_id, tag_id) is the primary key, the same tag cannot be attached to the same post twice. The database makes the duplicate impossible.
• Both foreign keys are ON DELETE CASCADE, so deleting a post automatically removes its rows here. The tags themselves survive, because other posts may still use them.

👨‍🏫 Whenever you see a table whose name looks like two other tables glued together (post_tags, order_items, user_roles), you are looking at a many-to-many relationship. Nine times out of ten it is also where the interesting bugs are, because it is the only part of the schema the application has to maintain by hand.

Reading a schema you did not write

You have three ways to learn a schema, and a good tester uses all three.

1. The schema file. In this project, db/schema.sql is 40 lines and tells you everything: the tables, the types, what is NOT NULL, what is UNIQUE, the foreign keys, and the indexes. When a project has one, read it first.

2. The database itself. \dt lists the tables, \d <table> describes one. This works on any database, including a production one where you have never seen the source code, which is exactly when you need it most.

3. The picture. For four tables, a sketch beats both:

categories                posts                         tags
----------                -----                         ----
id         PK   <-------- category_id  FK               id         PK
name       UNIQUE         id           PK               name       UNIQUE
slug       UNIQUE         title                         slug       UNIQUE
created_at                body                          created_at
                          created_at
                                  \                    /
                                   \   post_tags      /
                                    -- post_id  FK --
                                       tag_id   FK

Arrows point from the foreign key to the primary key it references. When you can draw that for an application, you can query it.

What the schema tells a tester before any test runs

Reading a schema is a testing activity in itself. Every constraint answers a question you would otherwise have to ask a developer, and every missing constraint is a test case:

👨‍🏫 That last row is the one I want you to remember. Half of "is this a bug?" arguments are settled in thirty seconds by reading the schema.

Commands overview 📖

\dt

Lists the tables in the current database.

Syntax:

\dt

Example:

\dt

\d

Describes a table: columns, types, nullability, defaults, indexes, and foreign keys.

Syntax:

\d <table-name>

Example:

\d post_tags

Suggested content 📚

• Data Definition - official PostgreSQL documentation
• Constraints - official PostgreSQL documentation
• Foreign keys - official PostgreSQL documentation

Exercise 🎯

Without running a single SELECT, use \d on all four tables and answer these, writing your answers down before you check the spoiler:

1. Which columns in the whole schema are UNIQUE?
2. If someone deletes the Databases category, what happens to its posts?
3. If someone deletes a post that has three tags, how many rows disappear from post_tags, and how many from tags?
4. Can two different posts have exactly the same title?

🙊 Here are the answers:

1. categories.name, categories.slug, tags.name, and tags.slug. Plus the primary keys, which are unique by definition: categories.id, posts.id, tags.id, and the pair (post_id, tag_id) in post_tags.
2. Nothing is deleted. ON DELETE SET NULL means the posts survive with category_id set to NULL. They then appear as posts with no category.
3. Three rows disappear from post_tags, because of ON DELETE CASCADE. Zero rows disappear from tags: the tags themselves belong to the whole blog, not to that post, and other posts may still be using them. Finding the tags that no longer belong to any post is a fine exercise, and we do exactly that two lessons from now.
4. Yes. There is no UNIQUE constraint on posts.title, so duplicate titles are allowed by design. We will create a pair of them on purpose later, and then find them with a query.

Show the world what you learned 🌎

To show your professional network what you learned in this lesson, post the following on LinkedIn.

I am taking the "SQL for Testers" course by @Walmyr Lima e Silva Filho at the @Talking About Testing School, where I learned to read a database schema like a tester: primary keys, foreign keys, one-to-many and many-to-many relationships, and what every constraint tells you about the application before a single test runs. #TalkingAboutTesting #TATSchool #SQLForTesters #SQL #PostgreSQL #Testing

👨‍🏫 Remember to tag me in your post. Here is my LinkedIn profile.