Database Schema
The defined structure and integrity rules that describe how a database organizes and relates its stored data.
Download Markdown (.md)Soroush’s Product Wiki · https://wiki.soroush.uk/topic/database-schema/
01What 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.[1][2]
Indexes and views support access patterns but should not be confused with the core meaning of the data model.
02Why 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.
03When 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.
04How it works
- Identify entities, attributes, relationships, lifecycle rules, and queries from the product domain.
- Choose appropriate data types, identifiers, nullability, defaults, and representation conventions.
- Define structural rules such as uniqueness, references, checks, and validation policies.
- Add indexes and views for justified access patterns without confusing them with the core model.
- Review the schema with application, analytics, privacy, security, and operational stakeholders.
- Version and evolve it through tested migrations that preserve compatibility and data meaning.
05A 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.
06Where 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.