Everything that happens in an application is stored somewhere: store orders, user profiles, warehouse stock. It's stored not in files and not "inside the program" — behind almost every application stands a database. Whoever can look into it sees the product inside out: not "the screen says 'Paid'" but "the status in the orders table really is paid." This series teaches you to look. Let's start with the structure.

Why not Excel and not files

A table is a table, even in Excel, right? But a file has three fatal problems once thousands of people work with the data simultaneously:

  • Concurrent access. Two people bought the last item in the same second — who got it? A file answers "whoever got lucky"; a database answers strictly by the rules.
  • Reliability. If a program crashes mid-write, the file is left half-written. A database guarantees: an operation either completed entirely or didn't happen at all.
  • Search speed. Finding one order among ten million in a file means reading the whole file. A database finds it in milliseconds.

The program that provides all this is called a DBMS — a database management system. The most common: PostgreSQL, MySQL, Oracle, SQL Server. For our purposes they're similar: they all speak the same query language — SQL. The series uses universal SQL with a slight PostgreSQL accent.

A table: rows and columns

Data in a database lives in tables. A table resembles an Excel sheet with strict rules:

  • Columns are defined up front, each with a name and a type: id is a number, email is a string, created_at is a date-time. You can't write text into a numeric column — the database won't let you.
  • Rows are the data itself: one row = one customer, one order, one product.

Our running example for the whole series is an online store database:

customers                    orders
id | name    | email          id  | customer_id | status  | amount
1  | Anna    | anna@...       101 | 1           | paid    | 2500
2  | Boris   | boris@...      102 | 1           | created | 900
3  | Vera    | vera@...       103 | 2           | paid    | 1200

The primary key and relationships

Every table has a column (usually id) whose value is unique for each row — the primary key. It exists to point at a row unambiguously: names and emails can coincide or change; id never does.

Now the main part. Notice customer_id in the orders table: it's a reference to a customer's id. Order 101 belongs to Anna — not because it says "Anna" but because it stores the number 1. Such a reference column is called a foreign key. Data in a database is deliberately not duplicated: a customer's name is stored once in customers, and all their orders simply reference it. The email changed — one row is edited, not a thousand orders. These relationships are why such databases are called relational, and they're exactly why we'll need JOIN later in the series — the operation that assembles data from related tables.

Where the database lives and how to get in

A database is a server program: it runs on a separate machine, and the application connects to it over the network. To get access, a tester or developer needs four things: the server address, the database name, a login, and a password — issued on the project (and the test environment has its own, not production!).

You connect through a client — a program with a query window: the universal DBeaver, pgAdmin for PostgreSQL, the console psql. Whatever client you pick, the essence is the same: type an SQL query, hit "execute," see the result as a table.

Where this applies

Understanding "tables + relationships" is your map of the terrain. Opening an unfamiliar project database, you'll look for familiar landmarks: the users table, the product's main entity tables, the reference columns between them. A schema of a couple dozen tables stops being scary once you see not a pile of names but relationships: "orders reference customers, order items reference orders and products."

Where beginners stumble:

  • They think the database is the application. No: the application is code, the database is storage. The same application can point at a test or production database — and behave "differently" purely because of the data.
  • They search the database for what they see on the screen. The screen says "Paid," the database says paid: the interface translates and decorates. Look at the raw values.
  • They're afraid a read query will break the database. Reading (SELECT) doesn't change data — read boldly. Caution is for modification, which gets its own article in this series.

What to learn next. Time to write your first query — SELECT: pick, filter, sort.