Database Concepts – Tables, Keys, Schemas & Relationships

Tables – Where Data Lives

A table is the fundamental unit of storage in a relational database. Think of it as a spreadsheet: data is organised into rows (also called records or tuples) and columns (also called fields or attributes). Unlike a spreadsheet, however, a database table enforces strict rules — every column has a defined data type, and the database engine validates every row against those rules before accepting it.

Key terminology to know:

• Row / Record — one complete entry in the table (e.g. one employee).
• Column / Field — one attribute of every row (e.g. salary).
• Cell — the intersection of one row and one column — holds a single value.
• NULL — a special marker meaning "no value" or "unknown". Different from zero or an empty string.

Primary Key – The Unique Row Identifier

Every table should have a primary key — a column (or set of columns) whose value is unique for every row and never NULL. The primary key is the definitive way to identify a specific record. Without a primary key, you cannot reliably reference a single row.

-- The id column is the primary key — unique, auto-incremented integer
CREATE TABLE employees (
    id         INT          PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name  VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    salary     DECIMAL(10, 2)
);

Best practices for primary keys:

• Use AUTO_INCREMENT (MySQL) or SERIAL (PostgreSQL) — let the database assign IDs automatically. You never have to worry about duplicates.
• Keep it simple — a single integer column named id is the most common and practical primary key.
• Never use user-provided data as a PK — email addresses, usernames, or phone numbers can change. Integers cannot be taken back once assigned.
• Composite primary keys — using two or more columns together as a PK is valid, but usually a sign that a junction table is the right model.

Foreign Key – Linking Tables Together

A foreign key is a column in one table that references the primary key of another table. This link creates a relationship between the two tables and enforces referential integrity — the database ensures you cannot create a foreign key value that points to a row that does not exist.

-- Users table (parent)
CREATE TABLE users (
    id    INT         PRIMARY KEY AUTO_INCREMENT,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

-- Orders table (child) — references users
CREATE TABLE orders (
    id         INT          PRIMARY KEY AUTO_INCREMENT,
    user_id    INT          NOT NULL,
    total      DECIMAL(10, 2),
    order_date DATE,
    -- FK: user_id must match an id in the users table
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Relationship Types

Relationships between tables come in three patterns. Understanding them is essential for designing a good schema:

One-to-One (1:1)

Each row in Table A corresponds to exactly one row in Table B, and vice versa. This is relatively rare — often used to split a table for performance reasons or to separate sensitive data (e.g. a users table and a separate user_profiles table with extended personal information).

One-to-Many (1:N) — Most Common

One row in Table A can relate to many rows in Table B, but each row in Table B relates to only one row in Table A. This is the most common relationship in any database. Examples: one user has many orders; one department has many employees; one blog post has many comments.

Many-to-Many (M:N)

Rows in Table A can relate to many rows in Table B, and rows in Table B can relate to many rows in Table A. This requires a junction table (also called a bridge table or associative table) that holds pairs of foreign keys.

Example: students and courses — a student can enrol in many courses, and a course can have many students.

CREATE TABLE students (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    id    INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL
);

-- Junction table: holds student-course pairs
CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    enrolled_at DATE,
    PRIMARY KEY (student_id, course_id),          -- composite PK
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id)  REFERENCES courses(id)
);

Schemas – The Blueprint

A schema is the complete structural definition of a database — all its tables, columns, data types, constraints, indexes, and relationships. The schema is the blueprint that tells the database how data must be structured before any actual data is stored.

Schemas serve two purposes:

• Enforcement — the database engine validates every INSERT and UPDATE against the schema and rejects data that violates the rules (wrong type, NULL in a NOT NULL column, duplicate primary key, broken foreign key).
• Communication — a schema is also documentation. Reading a schema tells you exactly what data an application stores and how entities relate to each other.

-- Schema for a minimal e-commerce database
CREATE TABLE users (
    id         INT          PRIMARY KEY AUTO_INCREMENT,
    email      VARCHAR(255) UNIQUE NOT NULL,
    name       VARCHAR(100) NOT NULL,
    created_at DATETIME     DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id          INT            PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(200)   NOT NULL,
    price       DECIMAL(10, 2) NOT NULL,
    stock       INT            DEFAULT 0
);

CREATE TABLE orders (
    id         INT            PRIMARY KEY AUTO_INCREMENT,
    user_id    INT            NOT NULL,
    total      DECIMAL(10, 2),
    created_at DATETIME       DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
    id         INT            PRIMARY KEY AUTO_INCREMENT,
    order_id   INT            NOT NULL,
    product_id INT            NOT NULL,
    quantity   INT            NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Indexes – Speed Up Lookups

As your table grows to millions of rows, queries that filter on a column (e.g. WHERE email = 'alice@example.com') will slow to a crawl if the database has to scan every single row. An index is a separate data structure (usually a B-tree) that the database builds and maintains alongside your table to enable fast lookups by specific column values — like the index at the back of a book.

-- Without an index, this scans every row (full table scan)
SELECT * FROM users WHERE email = 'alice@example.com';

-- Create an index on the email column
CREATE INDEX idx_users_email ON users(email);

-- Now the query uses the index — fast even with millions of rows
SELECT * FROM users WHERE email = 'alice@example.com';