Database Design Engineering

Database Design Engineering

Acknowledgement: Shared by Peter Bamuhigire, techguypeter.com, +256 784 464178.

Use When

• Use when designing or reviewing relational or document-backed data architecture for SaaS platforms, ERP systems, APIs, analytics stores, or mobile sync. Covers domain modeling, tenancy, indexing, migrations, integrity, retention, and performance tradeoffs.

Do Not Use When

• The task is query tuning or production incident diagnosis without a schema-design decision; use the database operations or reliability skill.
• The datastore is a search index, cache, or vector store whose source-of-truth semantics belong elsewhere.

Required Inputs

Workflow

Model invariants and ownership, choose keys and relationships, enforce integrity in the database, design indexes from verified access paths, plan reversible expand-contract migrations, then test concurrency, rollback, and representative query plans.

Quality Standards

The schema enforces material invariants with types, constraints, and transactions rather than application convention alone. Each index supports a named query, and every migration states lock, backfill, compatibility, rollback, and validation behavior.

Outputs

Evidence Produced

References

• Use the references/ directory for deep detail after reading the core workflow below.

Use this skill when schema choices will shape correctness, performance, or future maintainability. It complements vendor-specific database skills by focusing on design logic that survives framework changes.

Load Order

1. Load world-class-engineering.
2. Load this skill to design the data model and lifecycle.
3. Load engine-specific parent skills afterward: mysql-engineering, mysql-operations, postgresql-engineering, or postgresql-operations.

Database Workflow

1. Model the Domain

Define:

• Core entities and their lifecycles.
• Ownership and tenancy boundaries.
• Transactional invariants.
• Reporting and audit requirements.
• Retention, archival, and deletion rules.
• The release and migration constraints for live data.

Model events and states, not just forms and screens.

2. Choose the Storage Shape

Prefer relational design when:

• Consistency matters.
• Multi-table invariants exist.
• Reporting and joins are central.

Prefer document or key-value structures for:

• Flexible metadata.
• Cached projections.
• Denormalized read models or unstructured payloads.

Do not use schemaless storage as a substitute for undecided modeling.

3. Define Table Boundaries

• One table should represent one durable business concept or event stream.
• Separate current state from append-only history when auditability matters.
• Distinguish master data, transactions, and derived projections.
• Avoid overloading one table with mutually exclusive concepts.

4. Design for Scale and Safety

• Add tenant or ownership keys early.
• Design indexes around real query predicates and sort order.
• Keep writes idempotent where retries are possible.
• Plan archival, purging, and partitioning before large-volume tables arrive.
• Use expand-contract migrations for live systems.
• Separate transactional truth from projections, integrations, and analytical read models.
• Design test-data and backfill strategies for major schema evolution before the first risky migration.

Core Standards

Integrity

• Enforce invariants at the strongest reasonable layer: schema, transaction, application, worker.
• Use foreign keys where ownership is mandatory and module boundaries permit it.
• Use append-only ledgers for financial and audit-critical domains.
• Never encode important business rules only in UI validation.

Tenancy

• Every tenant-scoped table must carry the tenant key.
• Every tenant-scoped query must constrain by tenant key.
• Every unique constraint must reflect tenancy where appropriate.
• Cross-tenant analytics should use controlled derived datasets, not raw shared queries.

Indexing

• Index for the access path, not for column popularity.
• Prefer composite indexes that match filter plus sort order.
• Remove duplicate or low-value indexes that tax writes.
• Validate index usefulness against actual queries and execution plans.

Migrations

• Make every migration forward-safe and observable.
• Prefer additive changes, backfills, then cutovers, then cleanup.
• Keep application code compatible across deployment overlap where possible.
• Rehearse destructive or high-volume changes before production.
• Tag migrations with rollback posture: reversible, compensating-only, or forward-fix-only.
• For web projects, provide a root pull-time migration script that reads the app's environment database settings, compares tracked migrations with live migration history, and applies only missing migrations.
• Never bundle seeds into the normal migration-apply path. Demo data, reference seeds, fixtures, and production bootstrap scripts must require a separate explicit command.

Operable Data Systems

• Emit release markers and migration identifiers into logs and dashboards where possible.
• Track replication lag, queue lag, backfill progress, and lock or saturation risk on critical stores.
• Treat long-running migrations and backfills as operational workflows with owners and stop conditions.
• Document how data correctness will be verified after migration, replay, or recovery.

Scale, Replication, and Change Data

• Know which queries must read current truth and which can tolerate replica lag.
• Design outbox, CDC, or projection pipelines so integration needs do not corrupt the transactional model.
• Budget storage, index, and retention cost for hot tables before volume becomes painful.
• Validate the hottest access paths with execution plans, cardinality assumptions, and realistic filters.

Decision Heuristics

Normalize when:

• Data duplication would create correctness risk.
• Multiple workflows update the same fact.

Denormalize when:

• Read performance or query simplicity clearly outweighs duplication cost.
• The source of truth remains obvious.
• Refresh or reconciliation strategy is explicit.

Use soft delete when:

• Recovery, auditability, or legal hold matters.

Use hard delete when:

• Data minimization or cost pressure is stronger and downstream references are safely handled.

Deliverables

For substantive database work, produce:

• Entity and lifecycle summary.
• Table or collection design.
• Index plan.
• Migration strategy.
• Data verification and rollback posture.
• Data retention and audit plan.
• Top 5 critical queries or access patterns.
• Projection, outbox, or replication notes where external reads or integrations exist.

Review Checklist

• Domain entities reflect real business concepts.
• Tenant and ownership boundaries are explicit.
• Invariants are protected at durable layers.
• Indexes match real access paths.
• Migrations support live deployment safety.
• Migration verification and rollback posture are explicit.
• Audit, retention, and archival rules are documented.
• Reporting needs do not distort the transactional model without justification.
• Replica lag, projections, or CDC assumptions are explicit where they matter.

References

• references/data-review-checklist.md: Schema and migration review prompts.
• references/live-data-evolution.md: Expand-contract, backfills, verification, and rollback posture.
• ../../sdlc-meta/world-class-engineering/references/source-patterns.md: Source-derived patterns for design quality and website/data analysis.

Capability contract

Design schemas and migration plans by default. Apply DDL, backfills, retention jobs, or production queries only with explicit database and environment authority.

Degraded mode

If workload samples or schema access are unavailable, provide a read-only logical design and mark index, capacity, and migration claims unverified.

Domain Anti-Patterns

• Omitting foreign-key or application integrity rules. Fix: state enforcement explicitly.
• Indexing every column. Fix: derive indexes from measured access paths.
• Mixing tenant data without a tenant key. Fix: enforce isolation in schema and queries.
• Shipping destructive migrations in one step. Fix: use expand-contract.
• Retaining data indefinitely. Fix: define retention and deletion ownership.