Schema Design Basics

Schema design is deciding what tables exist, what columns they contain, how rows relate, and which rules the database must enforce. Good schema design makes PHP code simpler because the database rejects impossible data.

Application validation is still necessary, but it is not enough. Imports, scripts, admin tools, bugs, and future code paths can bypass one PHP form. Important data rules should also live in the database.

A Small Order Schema

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
    total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users (id)
);

CREATE INDEX orders_user_id_created_at_idx
    ON orders (user_id, created_at);

This schema shows the main building blocks: a primary key, foreign key, required columns, check constraints, timestamps, and an index.

Primary Keys

A primary key identifies one row. PHP code, URLs, relationships, audit logs, and background jobs often refer to rows by primary key.

Most PHP applications use integer IDs, UUIDs, or ULIDs. The best choice depends on database engine, scale, public exposure, and team conventions. The important beginner rule is: every normal application table should have a stable primary key.

Foreign Keys

A foreign key protects a relationship between tables. If orders.user_id references users.id, the database can reject orders for users that do not exist.

Foreign keys are especially useful for job-relevant bugs:

• Deleting a user while orders still reference them.
• Importing records in the wrong order.
• Creating child rows with invalid parent IDs.
• Accidentally leaving orphaned data.

Constraints

Constraints are database rules. Common constraints include:

• NOT NULL: a value is required.
• UNIQUE: no two rows may share the same value.
• CHECK: a value must match a rule.
• Foreign keys: a referenced row must exist.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    role TEXT NOT NULL CHECK (role IN ('user', 'admin')),
    created_at TEXT NOT NULL
);

If email must be unique, make it unique in the database. Checking in PHP before insert is not enough because two requests can arrive at the same time.

Indexes

Indexes help the database find and sort rows efficiently. They should match real query patterns.

CREATE INDEX orders_status_created_at_idx
    ON orders (status, created_at);

This index makes sense if the application often asks for recent orders by status:

SELECT id, total_cents
FROM orders
WHERE status = :status
ORDER BY created_at DESC
LIMIT 20;

Indexes are not free. They take space and slow down writes. Add them for known access patterns.

Timestamps

Most application tables need timestamps:

• created_at: when the row was created.
• updated_at: when the row was last changed.
• deleted_at: nullable soft-delete marker when the app keeps deleted records.

Use a real date/time type in production databases. Store a consistent timezone convention, usually UTC for application timestamps.

What To Check

Before moving on, make sure you can:

• Explain why most tables need a primary key.
• Use foreign keys to protect relationships.
• Choose constraints for required, unique, and limited values.
• Add indexes for actual query patterns.
• Include timestamps that support debugging and product behaviour.
• Explain why database rules complement PHP validation.