What are the core principles of good relational database schema design?

Explanation & Concept

Effective database design requires balancing data integrity, normalization, and query performance:

1. Use appropriate data types (e.g. `TIMESTAMPTZ` for dates, `INT` or `BIGINT` for keys, `VARCHAR(n)` instead of arbitrary length). 2. Enforce primary keys on every table and use foreign keys to maintain referential integrity. 3. Normalize to 3NF to avoid redundancy, then denormalize selectively for heavy read workloads. 4. Design naming conventions consistently (e.g., snake_case, singular vs plural table names). 5. Plan indexing strategies around actual application query patterns.

Code Example

sql

-- Production-grade user entity schema
CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    uuid UUID DEFAULT gen_random_uuid() UNIQUE NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE INDEX idx_users_created_at ON users (created_at DESC);

How It Works & Key Takeaways

Using an internal numeric `id` provides compact, fast B-Tree indexing for foreign key joins, while public APIs expose the opaque `uuid` to avoid exposing auto-incrementing sequential customer counts.