Relations and Tables: The Foundation of Relational Databases

When Edgar Codd introduced the relational model, he chose a brilliant abstraction: the table. While mathematicians see relations as sets of tuples, most database users visualize data as rows and columns in a grid—a spreadsheet-like structure that maps onto human intuition about organized information.

This table metaphor is both the relational model's greatest strength and a source of subtle confusion. Tables are intuitive, making databases accessible to non-mathematicians. But tables as commonly understood (think Excel spreadsheets) differ from formal relations in important ways.

This page examines the relation-as-table interpretation, exploring how abstract mathematics becomes concrete data structures, what's gained in the translation, and what conceptual precision is sometimes lost.

A database table is the physical manifestation of a relation. Let's dissect its components and map them to their formal counterparts.

Visual Representation:

Consider a table storing employee information:

Component Mapping:

Reading the Table:

Each row represents a fact. Row 1 asserts: 'Employee 1001, named Alice Chen, works in Engineering, earns $95,000, and was hired on March 15, 2020.'

Each column represents a property. The salary column contains all salary values, allowing questions like 'What's the average salary?' or 'Who earns the most?'

When tables faithfully implement relations, they inherit fundamental properties that ensure data integrity and query predictability. Understanding these properties helps you design better tables and debug unexpected behaviors.

Violations and Consequences:

When these properties are violated, problems arise:

SQL databases implement relations as tables, but with pragmatic deviations from pure relational theory. Understanding these differences is crucial for avoiding surprises and leveraging SQL effectively.

Deep Dive into Key Differences:

1. Duplicate Rows (Set vs. Bag Semantics)

Mathematically, relations are sets—duplicates are impossible by definition. SQL tables are bags (multisets)—duplicates are allowed unless explicitly prohibited.

-- This creates a table that can have duplicate rows:
CREATE TABLE orders (
    customer_id INT,
    product_id INT,
    quantity INT
);

-- Two identical inserts create two rows:
INSERT INTO orders VALUES (101, 200, 5);
INSERT INTO orders VALUES (101, 200, 5);  -- Duplicate allowed!

-- Use PRIMARY KEY or UNIQUE to enforce relation semantics:
CREATE TABLE orders_proper (
    order_id INT PRIMARY KEY,  -- Guarantees uniqueness
    customer_id INT,
    product_id INT,
    quantity INT
);

2. Row Ordering

Relations have no inherent ordering. SQL tables appear to have an order (the order rows were inserted, the physical storage order), but:

• This order is not guaranteed by SQL queries
• Queries without ORDER BY return rows in undefined order
• Relying on implicit order is a bug waiting to happen

3. NULL Values

Codd's original model had no NULLs. Later extensions addressed missing information, but NULL introduces three-valued logic complexities (covered in Page 2). SQL embraces NULL fully, for better or worse.

The CREATE TABLE statement translates a relation schema into a physical table. Let's examine this translation with attention to detail.

The General Form:

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...,
    [table_constraints]
);

Comprehensive Example:

Component Breakdown:

How you mentally visualize tables affects how you design and query them. Different mental models suit different purposes.

Mental Model 1: The Grid (Spreadsheet View)

The most common visualization: rows as horizontal entries, columns as vertical categories. This works well for:

• Understanding data layout
• Manual data entry concepts
• Simple queries (SELECT * FROM table)

Limitation: Encourages thinking about positions rather than meanings.

Mental Model 2: The Record Collection

Visualize each row as a complete record or document:

Employee Record #1001:
├── Name: Alice Chen
├── Department: Engineering  
├── Salary: $95,000
└── Hire Date: 2020-03-15

Employee Record #1002:
├── Name: Bob Smith
├── Department: Marketing
├── Salary: $72,000
└── Hire Date: 2019-07-01

This works well for:

• Understanding entities
• Designing forms and UIs
• Thinking about what a 'complete' record needs

Limitation: Can obscure cross-record operations (aggregations, joins).

Mental Model 3: The Predicate View

Visualize the table as asserting propositions:

Employee(1001, 'Alice Chen', 'Engineering', 95000, '2020-03-15') ← TRUE
Employee(1002, 'Bob Smith', 'Marketing', 72000, '2019-07-01') ← TRUE
Employee(1001, 'Alice Chen', 'Sales', 60000, '2020-03-15') ← FALSE (not in table)

This works well for:

• Understanding queries as questions
• Recognizing constraints as invariants
• Logic programming and theoretical analysis

Limitation: Too abstract for day-to-day work.

Mental Model 4: The Dimension View

Visualize each column as a dimension in an n-dimensional space, with each row as a point:

                           Salary ($K)
                              ↑
                              |
                     • Carol (105K)
                              |
                  • Alice (95K)
                              |
               • Emma (78K)
            • Bob (72K)
         • David (68K)
              |
    ──────────────────────────────→ Experience (years)

This works well for:

• Understanding analytics and aggregations
• Data science and statistical thinking
• Identifying outliers and patterns

Limitation: Hard to visualize beyond 3 dimensions.

Recommendation: Develop fluency with multiple mental models. Use the grid view for structure, the record view for entity thinking, the predicate view for complex logic, and the dimension view for analytics.

The relational model deliberately separates logical structure from physical implementation. But understanding how tables map to storage helps optimize performance and troubleshoot issues.

Logical-Physical Separation:

From the logical perspective, a table is an unordered set of rows. From the physical perspective, those rows must be stored somewhere—in files, on disks, in memory. The DBMS manages this translation.

Common Storage Approaches:

The Principle of Physical Data Independence:

This is one of the relational model's core contributions: applications should not depend on physical storage choices. You should be able to:

1. Change storage structures (add indexes, reorganize) without modifying queries
2. Move data to different storage media without application changes
3. Partition and distribute tables without query rewrites

In Practice:

Most of the time, you interact with tables purely logically:

-- This query doesn't know or care about physical storage:
SELECT emp_name, salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC;

The DBMS decides whether to:

• Scan the whole table
• Use an index on department
• Use an index on salary
• Combine indexes

Your query is what you want. Physical storage determines how efficiently you get it.

Certain table structures recur across database designs. Recognizing these patterns accelerates design work and ensures you're applying proven solutions.

Pattern 1: Entity Table

Stores instances of a single entity type, one row per entity.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    company_name VARCHAR(100) NOT NULL,
    contact_email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Characteristics:

• Single primary key identifying each entity
• Attributes describe the entity
• Usually has timestamps for auditing

Pattern 2: Association Table (Junction/Bridge Table)

Represents many-to-many relationships between entities.

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    enrolled_date DATE NOT NULL,
    grade CHAR(2),
    PRIMARY KEY (student_id, course_id)  -- Composite key
);

Characteristics:

• Composite primary key (combines keys from both entities)
• Foreign keys to both related tables
• May have relationship-specific attributes (grade, date)

Pattern 3: Lookup/Reference Table

Stores a fixed set of valid values (enumeration).

CREATE TABLE order_statuses (
    status_code CHAR(3) PRIMARY KEY,
    status_name VARCHAR(50) NOT NULL,
    is_terminal BOOLEAN DEFAULT FALSE
);

-- Populated with fixed values:
INSERT INTO order_statuses VALUES 
    ('PEN', 'Pending', FALSE),
    ('PRO', 'Processing', FALSE),
    ('SHP', 'Shipped', FALSE),
    ('DEL', 'Delivered', TRUE),
    ('CAN', 'Cancelled', TRUE);

Characteristics:

• Rarely changes (reference data)
• Referenced by foreign keys from other tables
• Provides human-readable names for codes

Pattern 4: History/Audit Table

Tracks changes over time.

CREATE TABLE salary_history (
    history_id INT PRIMARY KEY,
    emp_id INT REFERENCES employees(emp_id),
    old_salary DECIMAL(12,2),
    new_salary DECIMAL(12,2) NOT NULL,
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(100)
);

Characteristics:

• Records events rather than current state
• Has temporal columns (when)
• Often has actor columns (who)
• Append-only (rarely updated or deleted)

Pattern 5: Hierarchical/Self-Referential Table

Represents tree structures within a single table.

CREATE TABLE org_units (
    unit_id INT PRIMARY KEY,
    unit_name VARCHAR(100) NOT NULL,
    parent_unit_id INT REFERENCES org_units(unit_id),
    level INT NOT NULL
);

Characteristics:

• Self-referencing foreign key
• NULL parent for root nodes
• Often includes computed level or path columns

We've explored how the abstract mathematical concept of a relation manifests as the concrete database table. Let's consolidate the key insights and establish best practices:

Key Concepts Mastered:

What's Next:

With tables understood as the concrete representation of relations, we'll next examine relation schemas in detail—the formal definitions that describe table structure independently of any particular data content.