Schema Design: Building the Foundation of Your Database

Introduction

Suppose a signup endpoint writes every submitted email into a users table. Without a constraint, two requests can create the same account address. A database constraint makes that rule hold even when data comes from scripts, background jobs, or another application:

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(320) NOT NULL UNIQUE
);

This guide covers data types, constraints, relationships, normalization, and schema changes. It assumes you know what a database is and have written basic SQL.

Choosing Data Types

The type you pick affects storage, speed, and what errors you can catch. It’s worth thinking about this.

Common Data Types

VARCHAR Without Length: Specify It

Use VARCHAR(n) when the database should enforce a maximum length for a value. In PostgreSQL, an unbounded VARCHAR behaves like TEXT and does not reserve its theoretical maximum size. Length limits and storage behavior differ across database engines, so choose a limit from the data domain rather than a storage guess.

-- Good: length is specified
email VARCHAR(255)

-- Unbounded text is valid when the domain has no useful fixed limit
description TEXT

Constraints: Rules That Enforce Themselves

Constraints are how you make the database enforce your rules. Without them, bad data gets in and causes problems later—sometimes silently, which is worse.

Primary Keys

Every table needs one. A primary key uniquely identifies each row. It’s how you reliably reference a specific record from other tables.

CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

NOT NULL

If a column must have a value, say so with NOT NULL. If you forget, NULLs sneak in, and your application has to deal with missing data everywhere.

-- Required
email VARCHAR(255) NOT NULL,

-- Optional
middle_name VARCHAR(100) NULL

UNIQUE

Unique constraints prevent duplicates. A table can have multiple UNIQUE constraints, unlike primary keys.

-- One email per user
email VARCHAR(255) NOT NULL UNIQUE,

-- One badge per employee
badge_number VARCHAR(20) UNIQUE

CHECK

CHECK constraints validate conditions before allowing an insert. This catches business logic errors at the database gate.

price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),

status VARCHAR(20) NOT NULL CHECK (status IN ('active', 'inactive', 'suspended')),

percentage DECIMAL(5, 2) NOT NULL CHECK (percentage >= 0 AND percentage <= 100)

DEFAULT Values

Defaults handle cases where no value is provided. They work well for timestamps, booleans, and status fields.

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

is_active BOOLEAN DEFAULT true,

status VARCHAR(20) DEFAULT 'pending'

The database evaluates the default expression at insert time, not at schema definition time. DEFAULT CURRENT_TIMESTAMP captures when the row was actually inserted, not when the table was created. This distinction matters for audit trails and data migration scenarios where rows are backfilled with explicit timestamps.

Not every column deserves a default. A column that should always be provided explicitly — email, price, order_number — should be NOT NULL without a default. Defaults are for columns where the system has a sensible automatic value and the application should not have to specify one every time. For timestamps, CURRENT_TIMESTAMP is almost always correct. For status fields, the default should reflect the most common initial state (pending, draft, active). For booleans, DEFAULT true works for opt-in fields; DEFAULT false for opt-out fields.

Defaults can mask application bugs. If your code always provides a value but you have a default, you never notice when the code path that skips the value gets exercised. On the other hand, a default can be a safety net for legacy code or bulk import operations where not every column gets specified. When choosing defaults, ask whether the default reflects the true intended state of a new record or whether it is hiding missing business logic.

For columns that change over time, consider whether updated_at should auto-update on every row change rather than being set once at insert. PostgreSQL supports DEFAULT CURRENT_TIMESTAMP for the initial value, but updating on change requires either a trigger or application-level logic. Some ORMs handle this automatically. If you rely on application code to set updated_at, make sure every code path actually does it — this is one of the most commonly forgotten fields in practice.

Relationships Between Tables

Relationships let you connect data across tables. The three types you’ll encounter most are one-to-one, one-to-many, and many-to-many.

erDiagram
    CUSTOMERS ||--o{ ORDERS : places
    ORDERS ||--|{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : "ordered in"
    CUSTOMERS {
        int customer_id PK
        varchar email
        varchar name
    }
    ORDERS {
        int order_id PK
        int customer_id FK
        decimal total
        timestamp created_at
    }
    ORDER_ITEMS {
        int order_id FK
        int product_id FK
        int quantity
        decimal unit_price
    }
    PRODUCTS {
        int product_id PK
        varchar sku
        varchar name
        decimal price
    }

When to Use One-to-One

One-to-one suits optional data that only some rows need. User accounts with optional billing addresses, employees with optional parking assignments. If the optional data is large (long text, many columns) and most rows do not need it, keeping it separate avoids wasting space. If you always query the data together, merging into one table is simpler.

The clearest signal for one-to-one is optionality combined with low frequency. A user_profiles table where most users never fill in a bio, avatar, or preferred language belongs in a separate table linked by a unique foreign key. The join on read costs you nothing when most users have no profile row to find. But if you later find that 90% of users have filled in profiles, the join is happening almost every time and the one-to-one split has bought you nothing.

Another valid use case is access control boundaries. Sensitive fields like salary, social_security_number, or background_check_status are sometimes kept in a separate table with tighter row-level security permissions. The separate table limits which roles or applications can access those columns without complicating the main table’s permissions model.

Performance isolation is a third reason. If a column gets updated constantly (like a last_active timestamp that fires on every page view) but you never need it for reporting, keeping it in a separate table reduces lock contention on the main table during high-traffic periods. PostgreSQL’s row-level locking means updates to one row don’t block other rows in the same table, but keeping high-churn columns separate still reduces the working set size for queries that don’t need them.

When Not to Use One-to-One

If you find yourself joining the two tables in almost every query, merge them. One-to-one adds query complexity without benefit when the data is always needed together. If the relationship is actually “zero or one” on one side, consider whether that side just needs a nullable foreign key instead.

When to Use One-to-Many

One-to-many fits any parent-child relationship where children belong to exactly one parent. Customers and their orders. Categories and products. Authors and blog posts. The pattern is correct when a child can only have one parent and you frequently query children by parent or parent with children.

When Not to Use One-to-Many

If you need to query children from multiple parents together (like tags on posts where posts can have many tags and tags apply to many posts), you have a many-to-many, not one-to-many. Forgetting this distinction leads to data modeling bugs.

When to Use Many-to-Many

Many-to-many fits when entities associate in arbitrary combinations. Posts and tags. Students and courses. Products and categories. The junction table is not a failure of design — it accurately models the reality that these relationships exist independently of the entities themselves.

When Not to Use Many-to-Many

A junction table is still the usual choice when each side can relate to multiple rows. Arrays or repeated columns avoid a join, but make constraints and queries harder, and most relational databases cannot enforce a foreign key for each array element. Consider denormalizing only after measuring a real query bottleneck and documenting how the application will preserve those relationships.

One-to-One

One row corresponds to exactly one row in another table. A foreign key with a UNIQUE constraint makes this work. Sometimes it makes more sense to just merge the tables—it depends on whether you always use the data together.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL
);

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL UNIQUE REFERENCES users(id),
    bio TEXT,
    avatar_url VARCHAR(500),
    preferred_language VARCHAR(10) DEFAULT 'en'
);

The UNIQUE on user_id prevents a user from having more than one profile.

One-to-Many

The most common relationship. One user can have many orders, but each order has exactly one user.

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    order_number VARCHAR(50) NOT NULL UNIQUE,
    total DECIMAL(12, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Many-to-Many

Requires a junction table. An order contains many products, and a product can appear in many orders.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

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

The composite primary key (order_id, product_id) means each product shows up once per order—prevents duplicates too.

Practical Scenarios

Addresses

Embed all fields in one table if you’ll always query them together. Separate them out if different parts of your app need different addresses (shipping vs billing, for example).

-- Simple but inflexible
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    shipping_street VARCHAR(255),
    shipping_city VARCHAR(100),
    shipping_state VARCHAR(100),
    shipping_postal_code VARCHAR(20),
    shipping_country VARCHAR(100)
);

-- Reusable and flexible
CREATE TABLE addresses (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    address_type VARCHAR(20) CHECK (address_type IN ('shipping', 'billing')),
    street VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20) NOT NULL,
    country VARCHAR(100) NOT NULL
);

Categories and Tags

Single category? A simple reference works. Product in multiple categories? You need a junction table.

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    parent_id INTEGER REFERENCES categories(id)
);

CREATE TABLE product_categories (
    product_id INTEGER REFERENCES products(id),
    category_id INTEGER REFERENCES categories(id),
    PRIMARY KEY (product_id, category_id)
);

Audit Fields

Most tables should track who created and modified records.

CREATE TABLE sensitive_data (
    id SERIAL PRIMARY KEY,
    data_value VARCHAR(500),

    -- Audit trail
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER REFERENCES users(id),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_by INTEGER REFERENCES users(id),

    -- Soft delete
    deleted_at TIMESTAMP,
    deleted_by INTEGER REFERENCES users(id)
);

Security and Compliance Notes

• Use separate database roles for the application and schema migrations. Grant each role only the table and operation access it needs; use row-level security where tenant or user access must be enforced in the database, and test that one tenant cannot query another tenant’s rows.
• Splitting sensitive columns into a separate table helps only when permissions enforce the boundary. Minimize personal data, restrict access to sensitive fields, encrypt data at rest where required, and keep encryption keys in a separate key-management system.
• Use parameterized queries for values; schema constraints validate stored data but do not prevent SQL injection. If dynamic SQL is needed, allowlist identifiers such as table or column names.
• Define retention and deletion rules for each data category. A soft delete leaves data available in the database, and deletion plans should account for audit records, replicas, and backups under the applicable retention requirements.

Normalization Trade-offs at a Glance

Observability Checklist

• Track p95/p99 latency and slow-query counts for important queries; inspect plans when a query shifts from an index scan to a full scan or adds expensive joins.
• Monitor table and index size, row growth, index usage, and bloat to catch unexpected growth or maintenance load.
• Watch integrity signals such as constraint violations, duplicate-key failures, null rates in required fields, and orphan checks after bulk loads.
• During schema changes, track migration progress, lock waits, blocked queries, and replication lag. Set limits for the last three and pause the rollout if they are exceeded.

Common Production Failures

Referential integrity breaks: Teams sometimes disable foreign key constraints to speed up bulk loads, then forget to re-enable them. Or they re-enable but skip the validation step. Orphaned rows pile up quietly. After any bulk load operation, run a query that checks for orphaned foreign keys before calling it done.

Constraint violation cascades: CASCADE DELETE on a table with millions of child rows will lock the database long enough to cause an incident. I have seen this happen. Test cascade behavior with production-scale data before it reaches production. Batched deletes or soft deletes are safer for large child tables.

Slow parent updates and deletes: A foreign key does not automatically create an index on the referencing column in every database. Without one, changing or deleting a parent key can require scanning the child table to find matching rows. Frequent updates and deletes can also create dead tuples in MVCC databases; monitor table and index bloat separately.

Wrong data type choices: VARCHAR(255) for a country code is lazy. Every row wastes space. VARCHAR(2) for country_code stores the same two characters correctly in a tiny fraction of the space. Guess the actual maximum length, do not just copy-paste 255.

Common Pitfalls / Anti-Patterns

Using a type without a domain limit: If a field has a meaningful maximum length, encode it in a constraint such as VARCHAR(n) or a CHECK. In PostgreSQL, unbounded VARCHAR behaves like TEXT; neither type reserves the maximum possible string length.

Naming tables and columns inconsistently: Mixing snake_case, camelCase, and PascalCase across tables makes queries fragile and ORM mapping error-prone. Fix: adopt snake_case for PostgreSQL and enforce it through a schema linting tool.

Ignoring the created_at / updated_at audit fields: Tables without these fields make it impossible to audit when data was created or changed. Fix: add created_at TIMESTAMP DEFAULT NOW() and updated_at TIMESTAMP to every table that tracks persistent data.

Using TEXT for every field: A broad text type may accept values outside the domain and make invalid data harder to catch. Fix: choose types that describe the value, then use a length limit or CHECK when the domain has one. Storage details depend on the database engine.

Dropping tables without verifying foreign key dependencies: A DROP TABLE on a referenced parent table usually fails unless you remove or cascade the dependent constraints. Review dependencies and the effects of CASCADE before a destructive migration.

Quick Recap Checklist

• Choose data types that match the domain and expected value range.
• Enforce required fields, uniqueness, valid values, and relationships with constraints.
• Model one-to-one, one-to-many, and many-to-many relationships accurately.
• Normalize to prevent update anomalies; denormalize only for a measured need.
• Plan indexes, audit fields, deletion behavior, and migrations for production data size.

Interview Questions

Further Reading

• Normalization: Organizing Relational Data — how normalization reduces update anomalies
• Database Indexes — how indexes affect query and write performance
• Database Migration Strategies — planning changes to schemas that already hold production data
• PostgreSQL Documentation: The SQL Language - Data Definition — Comprehensive guide to table creation, constraints, and schema design in PostgreSQL
• MySQL 8.0 Reference Manual: Using SQL to Modify Tables — MySQL-specific schema modification patterns
• “Choosing a Wrong Primary Key” by_DATABAiley — Analysis of real-world problems from bad key choices
• “The Great Primary Key Debate” - Natural vs Surrogate Keys — Comprehensive comparison with production case studies

Conclusion

A well-designed schema is the foundation everything else builds on. The time you invest in getting tables, data types, constraints, and relationships right pays back with every query you write and every time your application needs to scale. Start with clear entities, enforce rules at the database level, model relationships accurately, and revisit decisions as your domain knowledge grows. The fundamentals covered here—tables as entity representations, thoughtful data type selection, constraint-driven data integrity, and correct relationship cardinality—apply regardless of which database you use. Master these and everything more advanced becomes easier.