Skip to content

The Relational Model

Tables, rows, columns, keys, and the mathematical foundation of relational databases.

What you'll learn

  • How data is organized into tables, rows, and columns
  • What primary keys and foreign keys are and why they matter
  • The difference between a schema and a table
  • How the relational model connects tables through relationships

Concept

Tables, Rows, and Columns

The relational model, introduced by Edgar F. Codd in 1970, organizes data into tables (also called relations). Each table has a set of named columns (attributes) that define what kind of data is stored, and each row (tuple) is a single record. For example, a users table might have columns id, name, and email, and each row represents one user.

Primary Keys

A primary key is a column (or combination of columns) that uniquely identifies each row in a table. No two rows can share the same primary key value, and it can never be NULL. Common choices are an auto-incrementing integer (id) or a UUID. The primary key is the anchor that lets you refer to a specific row from anywhere else.

Foreign Keys and Relationships

A foreign key is a column in one table that references the primary key of another table. This is how the relational model expresses relationships. For example, an orders table might have a user_id column that references users.id. The database enforces this link — you cannot insert an order with a user_id that does not exist in the users table.

Schema

A schema is the blueprint of your database: it defines every table, every column (with its data type), every constraint, and every relationship. The schema is fixed at design time (though it can be changed with migrations). This rigidity is a feature — it guarantees that every row conforms to the expected shape, preventing a whole class of data-quality bugs.

Example

              -- users table with a primary key
CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

-- orders table with a foreign key to users
CREATE TABLE orders (
  id        SERIAL PRIMARY KEY,
  user_id   INTEGER NOT NULL REFERENCES users(id),
  total     DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);
            

SERIAL auto-generates integer IDs. REFERENCES users(id) creates a foreign key — every order must belong to a real user.

Try it

Common mistakes

The mistake

Forgetting to define a primary key

The fix

Every table should have a primary key. Without one, you cannot reliably identify or update individual rows.

The mistake

Storing relationships as comma-separated values

The fix

Use a foreign key column or a junction table instead. CSV in a single column breaks joins and integrity.

Practice The Relational Model

2 exercises

Practice The Relational Model

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors