Schema Design Best Practices

What is Schema Design?

Schema design is the process of defining your database structure: tables, columns, data types, constraints, and relationships. Good schema design leads to efficient queries, data integrity, and maintainable systems.

Key Principles

-- Use descriptive, consistent names
CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Always define relationships explicitly
ALTER TABLE enrollments
  ADD CONSTRAINT fk_student
  FOREIGN KEY (student_id) REFERENCES students(id);

Best Practices

1. Normalize — Follow normal forms (at least 3NF) to reduce data duplication.
2. Use Proper Types — Use NUMERIC for money, TIMESTAMPTZ for timestamps, TEXT for unbounded strings.
3. Constraints — Add NOT NULL, CHECK, UNIQUE, and FK constraints to enforce data integrity.
4. Naming — Use snake_case, singular table names, and descriptive column names.
5. Primary Keys — Every table should have a primary key.

Key Points

1. created_at / updated_at — Add timestamp columns to track when rows are created and modified.
2. Avoid Reserved Words — Do not name columns 'user', 'order', 'group' without quoting.
3. Index Foreign Keys — PostgreSQL does not auto-index FK columns.
4. Use TIMESTAMPTZ — Always use TIMESTAMPTZ (with timezone) instead of TIMESTAMP.
5. Soft Deletes — Consider a deleted_at column instead of hard DELETE for audit trails.