# Database Schema

The defined structure and integrity rules that describe how a database organizes and relates its stored data.

- Type: artifact
- Domain: Engineering & architecture
- Level: Intermediate
- Last reviewed: 2026-09-23
- Original: [Soroush’s Product Wiki](<https://wiki.soroush.uk/topic/database-schema/>)

## What it means

A database schema is the formal structure applied to stored data, including collections or tables, fields or columns, types, keys, relationships, constraints, and sometimes views and indexes. In some relational products, schema also names a namespace inside a database; that narrower use should be distinguished from the overall data design.

## Why it matters

A schema turns product assumptions into enforceable and reviewable structure. It protects data quality, supports predictable queries, communicates relationships, and provides a basis for safely evolving applications and reporting over time.

## When to use it

Define an explicit schema wherever durable data has expected shape, identity, relationships, or invariants, including databases marketed as flexible or schemaless. Allow flexibility deliberately for genuinely variable attributes rather than leaving core product rules implicit.

## How it works

1. Identify entities, attributes, relationships, lifecycle rules, and queries from the product domain.
2. Choose appropriate data types, identifiers, nullability, defaults, and representation conventions.
3. Define structural rules such as uniqueness, references, checks, and validation policies.
4. Add indexes and views for justified access patterns without confusing them with the core model.
5. Review the schema with application, analytics, privacy, security, and operational stakeholders.
6. Version and evolve it through tested migrations that preserve compatibility and data meaning.

## A practical example

A booking schema defines reservations with a stable identifier, required account reference, start and end timestamps, allowed status values, and a rule that the end follows the start. These constraints reject invalid states regardless of which application writes the data.

## Where it breaks

A schema captures structure better than all business meaning, temporal rules, authorization, or cross-system truth. Overly rigid structures can slow legitimate evolution, while permissive schemas shift failures downstream. Documentation can also drift from the structures actually enforced.

## Common mistakes

- Treating schema as documentation only instead of enforcing suitable constraints.
- Using vague strings for identifiers, money, time, and status without explicit conventions.
- Mirroring one user interface or object model instead of durable domain relationships.
- Changing structure without planning existing data, mixed application versions, and rollback or recovery.

## Visualization

[View the visualization on the website](<https://wiki.soroush.uk/topic/database-schema/#visualization-database-schema>)

## Sources

1. [Table Basics](<https://www.postgresql.org/docs/current/ddl-basics.html>) — PostgreSQL Global Development Group · Official technical documentation
2. [Constraints](<https://www.postgresql.org/docs/current/ddl-constraints.html>) — PostgreSQL Global Development Group · Official technical documentation

## Related topics

- [Database](<https://wiki.soroush.uk/topic/database/>)
- [Relational Database](<https://wiki.soroush.uk/topic/relational-database/>)
- [Database Migration](<https://wiki.soroush.uk/topic/database-migration/>)
