Stop 05 · SQL & Databases for QATopics · 7/8

Database Schema Reading

In short

Joining a new project means facing 80 tables you've never seen. Reading the schema quickly tells you where data lives and how tables connect, so your queries hit the right spot.

Understanding how tables relate through primary and foreign keys, visualized in an ER diagram — enough fluency to navigate an unfamiliar database quickly when designing test scenarios.

The purple blob on a railway platform reads a long paper chart pinned beside a train coach door.

Think of it like this

The train reservation chart

A reservation chart links each PNR to passengers, and each passenger to a coach and berth. Once you know those links, you can find anyone. A schema shows the same links between tables using keys.

Watch it happen

How a shop's tables connect

users

addresses

orders

order_items

payments

-> products

1/5users has a primary key, id. Every user row has a unique id.

The big ideas

What you need to know

Primary key

A column that uniquely identifies each row, usually id.

Foreign key

A column pointing to another table's primary key, like orders.user_id to users.id.

ER diagrams

Tools like DBeaver or pgAdmin can draw tables and their links as a diagram for you.

Read constraints

NOT NULL, UNIQUE and CHECK rules tell you what data the database itself will refuse.

See it in code

Walk through the code

sql

1-- Which columns does orders have?2SELECT column_name, data_type, is_nullable3FROM information_schema.columns4WHERE table_name = 'orders';5 6-- Which foreign keys point out of orders? (PostgreSQL)7SELECT kcu.column_name, ccu.table_name AS points_to8FROM information_schema.table_constraints tc9JOIN information_schema.key_column_usage kcu10  ON kcu.constraint_name = tc.constraint_name11JOIN information_schema.constraint_column_usage ccu12  ON ccu.constraint_name = tc.constraint_name13WHERE tc.constraint_type = 'FOREIGN KEY'14  AND tc.table_name = 'orders';

1/4List every column, its type and whether it can be NULL.

information_schema describes the database itself. The first query works in most databases; the second is PostgreSQL-specific.

Save yourself the pain

Common mistakes

Oops

Guessing column names and joining on whatever looks right.

Do this instead

Check the schema or ER diagram first. user_id and customer_id may mean different things.

Oops

Ignoring constraints when designing tests.

Do this instead

If a column is NOT NULL, the database blocks NULLs. Test that the app shows a friendly error first.

Your mission

Map a sample schema

~30 min

Quick check

orders.user_id refers to users.id. What is orders.user_id?

Go deeper

1Sketch an ER diagram for a simple sample database (e.g. users, orders, products).
2Learn to identify primary keys and foreign keys in an unfamiliar schema.
3Practice reading a real application's schema documentation to plan test queries.