Relational Database Concepts

Tables, rows, columns, and primary keys

The Relational Model

The relational model, introduced by Edgar F. Codd in 1970, revolutionized data management. It organizes data into tables (relations) where each table represents an entity, and relationships between entities are established through keys. This simple yet powerful concept has become the foundation for most business applications worldwide. Understanding these core concepts is essential, they're the building blocks of every relational database system, from SQLite to Oracle.

Tables: The Foundation of Relational Databases

A table (also called a relation) is a collection of related data organized in rows and columns. Think of it like a spreadsheet, but with strict rules and powerful capabilities.

-- Example: A Users table
┌────┬──────────┬─────────────────────┬─────┬────────────┐
│ id │ username │ email               │ age │ created_at │
├────┼──────────┼─────────────────────┼─────┼────────────┤
│ 1  │ alice    │ alice@example.com   │ 28  │ 2024-01-15 │
│ 2  │ bob      │ bob@example.com     │ 34  │ 2024-01-16 │
│ 3  │ charlie  │ charlie@example.com │ 22  │ 2024-01-17 │
└────┴──────────┴─────────────────────┴─────┴────────────┘

-- Each table has:
-- • A unique name (users)
-- • Defined columns (id, username, email, age, created_at)
-- • Zero or more rows (the actual data)

Table Characteristics

Unique name: Each table has a distinct name within the database
Defined structure: Columns are defined with specific data types
No duplicate rows: Each row should be uniquely identifiable
Atomic values: Each cell contains a single, indivisible value
Order doesn't matter: Rows and columns have no inherent order

Rows and Columns: The Building Blocks

Columns (Attributes/Fields)

Columns define the structure of the table. Each column has a name, a data type, and optional constraints. They represent the attributes or properties of the entity.

-- Creating a table with column definitions
CREATE TABLE products (
    id INTEGER PRIMARY KEY,           -- Integer, primary key
    name VARCHAR(200) NOT NULL,       -- String up to 200 chars, required
    price DECIMAL(10, 2),             -- Decimal with 10 digits, 2 after decimal
    stock_quantity INTEGER DEFAULT 0, -- Integer with default value
    is_active BOOLEAN DEFAULT TRUE,   -- True/False flag
    created_at TIMESTAMP              -- Date and time
);

-- Common data types:
-- INTEGER, BIGINT         : Whole numbers
-- DECIMAL, NUMERIC        : Precise decimal numbers
-- VARCHAR(n), TEXT        : Text strings
-- DATE, TIMESTAMP         : Dates and times
-- BOOLEAN                 : True/False
-- JSON                    : JSON data (in modern databases)

Rows (Records/Tuples)

Rows are the actual data entries in the table. Each row represents a single instance of the entity, one user, one product, one order, etc.

-- The products table with data
┌────┬──────────────┬────────┬────────────────┬───────────┬────────────┐
│ id │ name         │ price  │ stock_quantity │ is_active │ created_at │
├────┼──────────────┼────────┼────────────────┼───────────┼────────────┤
│ 1  │ Laptop       │ 999.99 │ 50             │ TRUE      │ 2024-01-10 │
│ 2  │ Mouse        │ 29.99  │ 200            │ TRUE      │ 2024-01-11 │
│ 3  │ Keyboard     │ 79.99  │ 0              │ FALSE     │ 2024-01-12 │
└────┴──────────────┴────────┴────────────────┴───────────┴────────────┘

-- Each row:
-- • Represents one product
-- • Must have values for all columns (or NULL if allowed)
-- • Should be uniquely identifiable by its primary key

Key Concept: Columns define what kind of data you can store (the schema), while rows contain the actual data instances. This separation of structure and data is fundamental to relational databases.

Primary Keys: Unique Identifiers

A primary key is a column (or combination of columns) that uniquely identifies each row in a table. It's the most important constraint in relational databases.

Primary Key Rules

Uniqueness: No two rows can have the same primary key value
Not NULL: Primary key values cannot be empty or NULL
Immutable: Once set, primary key values shouldn't change
One per table: Each table has exactly one primary key
Simple is better: Usually a single integer column (id)

-- Single column primary key (most common)
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

-- Auto-incrementing primary key (PostgreSQL)
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,  -- Auto-generates 1, 2, 3...
    customer_id INTEGER,
    order_date DATE
);

-- Composite primary key (multiple columns)
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)  -- Both together are unique
);

-- Natural vs Surrogate keys
-- Natural key: Uses real data (e.g., email, SSN)
CREATE TABLE employees (
    employee_number VARCHAR(10) PRIMARY KEY,  -- Natural key
    name VARCHAR(100)
);

-- Surrogate key: Artificial ID (preferred in most cases)
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,              -- Surrogate key
    employee_number VARCHAR(10),
    name VARCHAR(100)
);

Best Practice: Use surrogate keys (simple integer IDs) rather than natural keys. Natural keys can change (emails change, people get married and change names), but surrogate keys remain stable forever.

Modern Primary Keys: UUID vs Auto-Increment

While auto-incrementing integers (1, 2, 3...) are the traditional choice for primary keys, modern distributed systems often benefit from UUIDs (Universally Unique Identifiers), particularly UUID v7.

Auto-Incrementing Integers

The classic approach: each new record gets the next sequential number.

-- PostgreSQL auto-increment
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- Generates 1, 2, 3, 4...
    username VARCHAR(50)
);

-- MySQL auto-increment
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50)
);

-- Records get sequential IDs
┌────┬──────────┐
│ id │ username │
├────┼──────────┤
│ 1  │ alice    │
│ 2  │ bob      │
│ 3  │ charlie  │
└────┴──────────┘

Advantages

Simple and intuitive: Easy to understand and debug
Small size: 4-8 bytes, efficient storage and indexing
Ordered by insertion: Natural chronological sorting
URL-friendly: Clean URLs like /users/123

Disadvantages

Coordination required: Single database must generate IDs, bottleneck in distributed systems
Predictable: Exposes business information (user count, growth rate)
Enumeration attacks: Easy to guess valid IDs (/users/1, /users/2...)
Merge conflicts: Combining databases with same IDs requires renumbering
Not globally unique: ID 123 in one database ≠ ID 123 in another

UUID v7 (Modern Solution)

UUID v7 combines the benefits of UUIDs (globally unique) with time-ordered generation, making them ideal for distributed systems and modern architectures.

-- PostgreSQL with UUID v7 (using pg_uuidv7 extension)
-- Note: The uuid-ossp extension only supports v1, v3, v4, v5.
-- For UUID v7, use the pg_uuidv7 extension:
CREATE EXTENSION IF NOT EXISTS "pg_uuidv7";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
    username VARCHAR(50)
);

-- Alternative: PostgreSQL 18+ has built-in support (no extension needed):
-- id UUID PRIMARY KEY DEFAULT uuidv7()

-- Example UUIDs (time-ordered)
┌──────────────────────────────────────┬──────────┐
│ id                                   │ username │
├──────────────────────────────────────┼──────────┤
│ 018d3c5e-8f2a-7000-9c4b-3e8f9a2b1c4d │ alice    │
│ 018d3c5e-8f2b-7000-a1c2-4f9b3e8d2a1c │ bob      │
│ 018d3c5e-8f2c-7000-b2d3-5e1a4f9c3b2d │ charlie  │
└──────────────────────────────────────┴──────────┘

-- UUID v7 structure (128 bits / 16 bytes):
-- [timestamp: 48 bits][version: 4 bits][random: 12 bits]
-- [variant: 2 bits][random: 62 bits]

-- Can be generated anywhere without coordination
import uuid

user_id = uuid.uuid7()  # Python 3.13+ example
# 018d3c5e-8f2a-7000-9c4b-3e8f9a2b1c4d

Advantages

Globally unique: Can be generated anywhere, anytime, without conflicts
Time-ordered: Sorts chronologically like auto-increment (unlike UUID v4)
No coordination needed: Generate IDs in application code, microservices, or offline
Distributed-friendly: Perfect for microservices, sharding, and multi-region systems
Security: Non-predictable, prevents enumeration attacks
Database portability: Merge databases without ID conflicts
Client-side generation: Create IDs before inserting, helpful for complex workflows

Disadvantages

Larger size: 16 bytes vs 4-8 bytes, more storage and memory
Less readable: Hard to remember or communicate (018d3c5e-8f2a-7000...)
URL length: Longer URLs (/users/018d3c5e-8f2a-7000-9c4b-3e8f9a2b1c4d)
Index performance: Slightly slower than integers (but UUID v7 much better than v4)
Not all databases support: Older database versions may not have native UUID v7 support

When to Use Each

Scenario	Auto-Increment	UUID v7
Monolithic app, single database	✅ Perfect fit	⚠️ Overkill but fine
Microservices architecture	❌ Coordination nightmare	✅ Ideal solution
Distributed system, sharding	❌ Complex to coordinate	✅ Works perfectly
Public-facing IDs in URLs	⚠️ Security risk (enumeration)	✅ Safe and unpredictable
Multi-region replication	❌ ID conflicts	✅ No conflicts
Offline-first applications	❌ Can't generate without DB	✅ Generate anywhere
Database merging/importing	❌ Must renumber IDs	✅ No conflicts
Small tables, simple queries	✅ Fast and efficient	⚠️ Slight overhead

Hybrid Approach: Many systems use both. Auto-increment integers for internal IDs and database relationships, UUID v7 for public-facing identifiers. For example, internal user_id = 12345, but public user identifier = 018d3c5e-8f2a-7000-9c4b-3e8f9a2b1c4d exposed in APIs and URLs.

Modern Recommendation: If you're building a new system in 2024+, especially with microservices, APIs, or any distributed architecture, start with UUID v7. The flexibility and scalability benefits far outweigh the minor storage overhead. Auto-increment is fine for legacy monoliths or internal tables that never need distribution.

Foreign Keys: Creating Relationships

A foreign key is a column that references the primary key of another table, creating a relationship between tables. This is how relational databases "relate" data.

-- Parent table: customers
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255)
);

-- Child table: orders (references customers)
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Sample data demonstrating the relationship
customers:
┌─────────────┬────────────┬──────────────────┐
│ customer_id │ name       │ email            │
├─────────────┼────────────┼──────────────────┤
│ 1           │ Alice      │ alice@email.com  │
│ 2           │ Bob        │ bob@email.com    │
└─────────────┴────────────┴──────────────────┘

orders:
┌──────────┬─────────────┬────────────┬──────────────┐
│ order_id │ customer_id │ order_date │ total_amount │
├──────────┼─────────────┼────────────┼──────────────┤
│ 101      │ 1           │ 2024-01-15 │ 299.99       │
│ 102      │ 1           │ 2024-01-20 │ 499.99       │
│ 103      │ 2           │ 2024-01-18 │ 149.99       │
└──────────┴─────────────┴────────────┴──────────────┘

-- customer_id in orders table is a foreign key
-- It links each order to a specific customer

Foreign Key Constraints

Foreign keys enforce referential integrity, they prevent orphaned records and maintain data consistency.

-- Foreign key with actions
CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE CASCADE      -- Delete orders when customer deleted
        ON UPDATE CASCADE      -- Update orders if customer_id changes
);

-- Common referential actions:
-- CASCADE    : Automatically propagate changes to child records
-- RESTRICT   : Prevent deletion/update if child records exist (default)
-- SET NULL   : Set foreign key to NULL when parent deleted
-- SET DEFAULT: Set foreign key to default value
-- NO ACTION  : Same as RESTRICT

-- Example: Attempting invalid operations
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (104, 999, '2024-01-21');
-- ERROR: customer_id 999 doesn't exist in customers table

DELETE FROM customers WHERE customer_id = 1;
-- With CASCADE: Deletes customer AND all their orders
-- With RESTRICT: ERROR - customer has orders, cannot delete

Power of Foreign Keys: They maintain data integrity automatically. You can't create orders for non-existent customers, and you can't accidentally delete a customer who has orders (unless you explicitly allow it).

Types of Relationships

Foreign keys create three types of relationships between tables:

One-to-Many (Most Common)

One record in Table A can relate to many records in Table B, but each record in Table B relates to only one record in Table A.

Examples: One customer → many orders, One author → many books, One department → many employees

-- One-to-Many: One customer has many orders
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,  -- Foreign key
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Alice (customer_id=1) can have many orders
-- Each order belongs to exactly one customer

One-to-One (Less Common)

One record in Table A relates to exactly one record in Table B, and vice versa. Often used to split large tables or separate sensitive data.

Examples: User → User Profile, Person → Passport, Employee → Desk Assignment

-- One-to-One: One user has one profile
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(255)
);

CREATE TABLE user_profiles (
    profile_id INTEGER PRIMARY KEY,
    user_id INTEGER UNIQUE,  -- UNIQUE ensures one-to-one
    bio TEXT,
    avatar_url VARCHAR(500),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Many-to-Many (Requires Junction Table)

Multiple records in Table A can relate to multiple records in Table B. Requires a junction (bridge/join) table with foreign keys to both tables.

Examples: Students ↔ Courses, Products ↔ Categories, Authors ↔ Books (co-authors)

-- Many-to-Many: Students can take many courses, courses have many students
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    course_name VARCHAR(100)
);

-- Junction table (also called bridge table, join table)
CREATE TABLE enrollments (
    enrollment_id INTEGER PRIMARY KEY,
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date DATE,
    grade VARCHAR(2),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    UNIQUE(student_id, course_id)  -- Prevent duplicate enrollments
);

-- Now a student can enroll in many courses
-- And a course can have many students enrolled

Other Important Constraints

Beyond primary and foreign keys, databases offer additional constraints to maintain data quality and integrity:

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    
    -- NOT NULL: Column must have a value
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,

    -- UNIQUE: No duplicate values allowed
    email VARCHAR(255) UNIQUE,
    employee_number VARCHAR(10) UNIQUE,

    -- CHECK: Value must satisfy a condition
    age INTEGER CHECK (age >= 18 AND age <= 120),
    salary DECIMAL(10, 2) CHECK (salary > 0),

    -- DEFAULT: Automatic value if none provided
    hire_date DATE DEFAULT CURRENT_DATE,
    is_active BOOLEAN DEFAULT TRUE,
    department_id INTEGER,

    -- Foreign key
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- Constraint examples in action:
INSERT INTO employees (first_name, last_name, email, age, salary)
VALUES ('John', 'Doe', 'john@company.com', 25, 50000.00);
-- ✓ Valid

INSERT INTO employees (first_name, email, age, salary)
VALUES ('Jane', 'jane@company.com', 30, 60000.00);
-- ✗ ERROR: last_name is NOT NULL

INSERT INTO employees (first_name, last_name, email, age, salary)
VALUES ('Bob', 'Smith', 'john@company.com', 28, 55000.00);
-- ✗ ERROR: email must be UNIQUE

INSERT INTO employees (first_name, last_name, email, age, salary)
VALUES ('Alice', 'Johnson', 'alice@company.com', 16, 40000.00);
-- ✗ ERROR: age CHECK constraint failed (must be >= 18)

Constraint	Purpose	Example
PRIMARY KEY	Uniquely identifies each row	user_id, order_id
FOREIGN KEY	Links to another table	customer_id references customers
UNIQUE	No duplicate values	email, username
NOT NULL	Must have a value	first_name, email
CHECK	Value must meet condition	age >= 18, price >0
DEFAULT	Automatic value if none given	created_at DEFAULT NOW()

Understanding NULL Values

NULL is a special value that represents the absence of data. It's not zero, not an empty string, it means "unknown" or "not applicable."

-- NULL examples
CREATE TABLE contacts (
    contact_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),        -- Can be NULL (optional)
    email VARCHAR(255)        -- Can be NULL (optional)
);

-- Valid inserts with NULL
INSERT INTO contacts (contact_id, name, phone)
VALUES (1, 'Alice', '555-1234');
-- email is NULL (not provided)

INSERT INTO contacts (contact_id, name, email)
VALUES (2, 'Bob', 'bob@email.com');
-- phone is NULL (not provided)

-- NULL behavior
SELECT * FROM contacts WHERE phone = NULL;     -- ✗ Wrong! Returns nothing
SELECT * FROM contacts WHERE phone IS NULL;    -- ✓ Correct way to check NULL

-- NULL in calculations
SELECT name, phone, LENGTH(phone) FROM contacts;
-- For Bob, LENGTH(phone) returns NULL, not 0

-- Handling NULL with COALESCE
SELECT name, COALESCE(phone, 'No phone') AS phone FROM contacts;
-- Returns 'No phone' if phone is NULL

Common Pitfall: You cannot use = or != with NULL. Always use IS NULL or IS NOT NULL. This is because NULL represents "unknown," and you can't compare something to an unknown value.

Practical Example: E-Commerce Schema

Let's put it all together with a simplified e-commerce database schema:

-- Complete e-commerce schema
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0)
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price_at_purchase DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- Sample data
INSERT INTO customers (customer_id, email, first_name, last_name)
VALUES (1, 'alice@email.com', 'Alice', 'Smith');

INSERT INTO products (product_id, name, price, stock_quantity)
VALUES 
    (101, 'Laptop', 999.99, 50),
    (102, 'Mouse', 29.99, 200);

INSERT INTO orders (order_id, customer_id, total_amount)
VALUES (1001, 1, 1029.98);

INSERT INTO order_items (order_item_id, order_id, product_id, quantity, price_at_purchase)
VALUES 
    (1, 1001, 101, 1, 999.99),
    (2, 1001, 102, 1, 29.99);

-- Relationships in action:
-- • customers → orders (one-to-many)
-- • orders → order_items (one-to-many)
-- • products → order_items (one-to-many)
-- • orders + products ↔ order_items (many-to-many junction)

Schema Benefits: This design prevents data duplication (customer info stored once), maintains referential integrity (can't create orders for non-existent customers), and accurately tracks the price at the time of purchase (even if product prices change later).

Key Takeaways

Tables organize data - rows are records, columns are attributes with defined types
Primary keys are essential - every table needs a unique identifier for each row
Foreign keys create relationships - they link tables together and enforce referential integrity
Three relationship types: One-to-many (most common), one-to-one (rare), many-to-many (needs junction table)
Constraints protect data quality - NOT NULL, UNIQUE, CHECK prevent invalid data
NULL means unknown - it's not zero or empty, use IS NULL to check for it
Good design prevents problems - proper keys and constraints catch errors before they happen
Understanding these fundamentals is critical, every complex database system is built on these simple concepts of tables, keys, and relationships

Back to "Introduction to Databases"

Database FundamentalsLesson 2

Next: "SQL Basics"