The Relational Model

The Relational Model

The relational model was proposed by Edgar F. Codd at IBM in 1970 in his landmark paper "A Relational Model of Data for Large Shared Data Banks." Despite being over 50 years old, it remains the dominant model for storing and querying structured data because it maps naturally to how we think about entities and their relationships.

The central idea is simple: organize data into tables (called relations), where each table has a fixed set of columns and any number of rows. Tables are linked to each other through shared key values, allowing complex real-world relationships to be represented without duplicating data.

Tables, Rows, and Columns

Every piece of data in a relational database lives in a table. A table is a two-dimensional grid:

• Columns (also called attributes or fields) define the structure. Each column has a name and a data type that constrains what values it can hold. For example, a column named price of type NUMERIC(10,2) can hold decimal numbers up to 10 digits with 2 decimal places.
• Rows (also called tuples or records) are individual data entries. Each row represents one instance of the entity the table describes — one customer, one product, one order.

Consider a simple products table:

The table has 4 columns and 3 rows. Each row uniquely describes one product.

Primary Keys

A primary key is a column (or combination of columns) whose values uniquely identify each row in a table. No two rows can have the same primary key value, and the primary key column cannot be NULL.

In PostgreSQL, the most common primary key pattern is SERIAL PRIMARY KEY, which automatically generates a sequential integer for each new row:

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name       VARCHAR(200) NOT NULL,
    price      NUMERIC(10, 2) NOT NULL
);

SERIAL is shorthand for a sequence-backed INTEGER. In PostgreSQL 18, you can also use GENERATED ALWAYS AS IDENTITY, which is the SQL-standard equivalent and slightly more explicit.

Foreign Keys and Relationships

A foreign key is a column whose values must match an existing primary key in another table. Foreign keys enforce referential integrity — they prevent orphaned records (rows that reference a non-existent parent row).

There are three fundamental types of relationships between tables:

One-to-Many (1:N)

The most common relationship. One customer can have many orders, but each order belongs to exactly one customer. This is modeled by placing the foreign key on the "many" side:

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    ordered_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The customer_id column in orders references the customer_id primary key in customers. PostgreSQL will reject any INSERT or UPDATE that would set a customer_id value that does not exist in the customers table.

Many-to-Many (N:M)

When two entities can each relate to many instances of the other — orders can contain many products, and products can appear in many orders — a junction table (also called an associative or bridge table) is used:

CREATE TABLE order_items (
    order_id   INTEGER NOT NULL REFERENCES orders(order_id),
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity   INTEGER NOT NULL,
    unit_price NUMERIC(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

The junction table holds the foreign keys from both sides and uses a composite primary key to ensure each product appears at most once per order.

One-to-One (1:1)

One entity maps to exactly one of another. For example, a users table and a user_profiles table where each user has exactly one profile. This is modeled with a unique foreign key:

CREATE TABLE user_profiles (
    user_id    INTEGER PRIMARY KEY REFERENCES users(user_id),
    bio        TEXT,
    avatar_url VARCHAR(500)
);

Making the foreign key the primary key ensures the uniqueness constraint.

Entity-Relationship Diagrams

An Entity-Relationship (ER) diagram is a visual representation of a database schema. It shows the entities (tables), their attributes (columns), and the relationships between them. ER diagrams are the standard tool for designing and communicating database schemas before writing a single line of SQL.

Key notation:

• Rectangles represent entities (tables)
• Ovals represent attributes (columns)
• Diamonds represent relationships
• Lines with crow's foot notation show cardinality (1:1, 1:N, N:M)

For our e-commerce example, the ER diagram would show:

• customers — orders as a 1:N relationship (one customer, many orders)
• orders — order_items — products as N:M through the junction table

Data Types in PostgreSQL 18

Choosing the right data type for each column is a critical design decision. It affects storage size, query performance, and data integrity:

Always use NUMERIC for money, never FLOAT or REAL. Floating-point types cannot represent many decimal values exactly (0.1 + 0.2 ≠ 0.3 in floating-point arithmetic), which leads to rounding errors in financial calculations.

Use TIMESTAMPTZ (timestamp with time zone) rather than TIMESTAMP for any datetime that users in different time zones might interact with. PostgreSQL stores TIMESTAMPTZ values internally in UTC and converts to the session time zone on retrieval.

The Relational Model's Power

The real power of the relational model becomes clear when you start querying across multiple tables. Consider this question: "Which customers have placed orders worth more than $500 in total?" In a relational database, this is a single SQL query that JOINs three tables and aggregates the results. In a file system or a simple key-value store, answering this question would require loading all the data and writing custom application code.

The relational model, combined with SQL, gives you a declarative query language where you describe what you want rather than how to get it. The database's query planner figures out the most efficient execution strategy automatically.

Understanding the relational model deeply — tables, keys, relationships, and data types — is the foundation everything else in SQL builds upon. In the next lesson, you will install PostgreSQL 18 and create your first real tables.