Before writing any query, every data professional needs to understand how information is organized. The relational model is the foundation of practically every database you'll run into day to day, and mastering its concepts is the first step toward avoiding mistakes that are costly later.

The basic vocabulary: table, row and column

Think of a spreadsheet. A table represents a real-world entity — customers, products, orders. Each row (or record) is a specific occurrence of that entity: one particular customer. Each column (or attribute) describes a characteristic: name, email, sign-up date.

The golden rule is: a table is about one thing only. Mixing customer and order data in the same table is the kind of decision that seems practical today and becomes a nightmare tomorrow.

Primary key: the identity of each row

The primary key is the column — or set of columns — that uniquely identifies each row. Two customers can have the same name, but never the same id. It ensures you can point to a specific record without ambiguity.

In practice, a sequential number or a database-generated identifier is commonly used. What matters is that it's unique and doesn't change over time.

Foreign key: connecting tables

A foreign key is a column that points to the primary key of another table. That's how we say "this order belongs to that customer". It creates the link and, on top of that, guarantees referential integrity: the database prevents you from registering an order for a customer that doesn't exist.

CREATE TABLE clientes (
  id    INTEGER PRIMARY KEY,
  nome  VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE
);

CREATE TABLE pedidos (
  id         INTEGER PRIMARY KEY,
  cliente_id INTEGER NOT NULL,
  valor      DECIMAL(10,2),
  FOREIGN KEY (cliente_id) REFERENCES clientes(id)
);

The three types of relationship

Relationships describe how entities connect. There are three:

• 1:1 (one to one) — each record in A links to at most one in B. E.g.: a user and their extended profile. It's rare and can often become a single table.
• 1:N (one to many) — the most common. A customer has many orders, but each order belongs to a single customer. The foreign key sits on the "many" side.
• N:N (many to many) — students take many courses and each course has many students. This is where the junction table comes in.

The N:N case and the junction table

A relational database can't represent N:N directly. The solution is to create a third table, called a junction table (or associative table), that stores the pairs. It has two foreign keys, one for each side.

CREATE TABLE matriculas (
  aluno_id     INTEGER REFERENCES alunos(id),
  disciplina_id INTEGER REFERENCES disciplinas(id),
  PRIMARY KEY (aluno_id, disciplina_id)
);

Visualizing the relationship

A simple diagram helps cement the 1:N relationship between customers and orders:

  clientes                pedidos
 +--------+              +-------------+
 | id (PK)|◄────────────┤ cliente_id  |
 | nome   |   1     N    | id (PK)     |
 | email  |              | valor       |
 +--------+              +-------------+

Modeling well isn't about memorizing key types: it's about asking "what is each table about?" and "how do they connect?". Whoever answers that clearly designs schemas that can handle growth.

In interviews it's common to be asked to model a simple scenario — a store, a library — and identify the relationships. Day to day, this shows up every time you need to decide where a new column should live or why a JOIN is duplicating rows.

Conclusion

The relational model revolves around a few concepts: tables as entities, keys that identify and connect records, and relationships that structure the whole. With this solid foundation, writing queries and evolving the schema stops being trial and error. The natural next step is learning to query this data efficiently.