SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsdatabase-design-engineering

database-design-engineering

Data & analytics

View repository

Updated 10-16 08:03

About this skill

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.
  • The task needs reusable judgment, domain constraints, or a proven workflow rather than ad hoc advice.

Do Not Use When

  • The task is unrelated to database-design-engineering or would be better handled by a more specific companion skill.
  • The request only needs a trivial answer and none of this skill's constraints or references materially help.

Required Inputs

  • Gather relevant project context, constraints, and the concrete problem to solve; load references only as needed.
  • Confirm the desired deliverable: design, code, review, migration plan, audit, or documentation.

Workflow

  • Read this SKILL.md first, then load only the referenced deep-dive files that are necessary for the task.
  • Apply the ordered guidance, checklists, and decision rules in this skill instead of cherry-picking isolated snippets.
  • Produce the deliverable with assumptions, risks, and follow-up work made explicit when they matter.

Quality Standards

  • Keep outputs execution-oriented, concise, and aligned with the repository's baseline engineering standards.
  • Preserve compatibility with existing project conventions unless the skill explicitly requires a stronger standard.
  • Prefer deterministic, reviewable steps over vague advice or tool-specific magic.

Anti-Patterns

  • Treating examples as copy-paste truth without checking fit, constraints, or failure modes.
  • Loading every reference file by default instead of using progressive disclosure.

Outputs

  • A concrete result that fits the task: implementation guidance, review findings, architecture decisions, templates, or generated artifacts.
  • Clear assumptions, tradeoffs, or unresolved gaps when the task cannot be completed from available context alone.
  • References used, companion skills, or follow-up actions when they materially improve execution.

Evidence Produced

Category Artifact Format Example
Data safety Migration plan Markdown doc per skill-composition-standards/references/migration-plan-template.md docs/data/migration-2026-04-16-add-tenant-column.md
Data safety Entity model Markdown doc per skill-composition-standards/references/entity-model-template.md docs/data/entity-model-billing.md
Data safety Access pattern register Markdown doc per skill-composition-standards/references/access-patterns-template.md docs/data/access-patterns-billing.md

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