Database Schema Design

Tables, keys, constraints and indexes designed on purpose, migrations that ship without downtime, and the routines that keep a relational schema correct, documented and easy to change.

Free · Mac (Apple silicon) and Windows · v1.0.5 · Other platforms

Then open the Marketplace in the app, search for Database Schema Design, and click Install. It goes into your own files, and works offline from then on.

What it installs.

1

Pillar

1

Area

50

Projects

200

Starter tasks

⚙️ Software Engineering

Database Schema Design

Designing tables, indexes, relationships and migrations that keep data correct, queries fast and future changes possible without downtime.

  1. Entity map of the current database schema
  2. Naming conventions for tables, columns and constraints
  3. Primary key strategy decision
  4. Column data types and nullability rules
  5. Choosing a migration tool and workflow
  6. Foreign keys and referential integrity baseline
  7. Timestamps, time zones and audit columns standard
  8. Storing money, quantities and units exactly
  9. Entity relationship diagram generated from the schema
  10. Local and CI databases built from migrations
  11. Migration review checklist for pull requests
  12. Unused and duplicate index review
  13. Weekly review of schema-related slow queries
  14. Schema drift detection between environments
  15. Table growth and storage bloat log
  16. Monthly orphan row and constraint gap sweep
  17. Quarterly schema health review
  18. Migration rollback rehearsal
  19. Data dictionary for business-critical tables
  20. Deprecated column and table retirement register
  21. Normalisation practice on your own tables
  22. Reading query plans to choose indexes
  23. Composite and covering index design
  24. Locks and transactions during schema changes
  25. Modelling many-to-many and polymorphic relationships
  26. Partial, expression and unique index techniques
  27. Check constraints for business rules
  28. Modelling trees and hierarchies
  29. Expand and contract migrations for zero downtime
  30. Batched backfills on large tables
  31. Soft delete versus hard delete decision
  32. JSON columns versus normalised tables
  33. Enumerated values in lookup tables, enums or checks
  34. Splitting an oversized table into focused tables
  35. Adding constraints to large live tables safely
  36. Deliberate denormalisation with a sync rule
  37. Schema design review before a feature launch
  38. Database major version upgrade compatibility check
  39. Personal data map and erasure design for a privacy review
  40. Schema change freeze plan for a peak period
  41. Destructive migration rehearsal on a production copy
  42. Multi-tenant schema model decision
  43. Lean schema for a solo developer's first product
  44. Inheriting an undocumented schema with no constraints
  45. Keeping control of the schema while using an ORM
  46. Append-only history tables for audited records
  47. Reporting tables alongside the transactional schema
  48. Table partitioning by time or key
  49. Temporal and bitemporal data modelling
  50. Online schema change tools for very large tables

Where it starts.

Each project arrives with its purpose, milestones and first tasks written out. Here is how the first three read.

Entity map of the current database schema

Before changing anything, you need to know which tables exist, which ones the application still reads and how they relate, and most schemas have a few that nobody can explain. A single afternoon spent listing every table with its row count, owner and main relationships turns a folder of migration files into a picture you can reason about.

First tasks

  • Export the table list with row estimates from the statistics views
  • Note which feature or service owns each table
  • Check access statistics for tables with no recent reads or writes
  • Commit the entity map to the repository's docs folder

Naming conventions for tables, columns and constraints

Mixed conventions like userId beside created_at and tbl_orders beside invoice cost a few seconds on every query and cause real bugs when someone guesses the wrong name. Writing one page of rules for table plurality, case, foreign key names, index names and boolean prefixes settles the argument once and gives reviewers something to point at.

First tasks

  • List ten inconsistent names from the current schema as examples
  • Draft rules for case, plurality, key, index and constraint names
  • Decide which existing exceptions to leave alone and record why
  • Add the standard's link to the repository's contributing guide

Primary key strategy decision

Whether rows are keyed by an auto-increment integer, a random UUID, a time-ordered ID or a natural key affects index size, insert speed, URL exposure and how hard it is to merge data later. Deciding once, with the reasons written down, stops each new table from reopening the question and avoids the painful key migration two years in.

First tasks

  • List the key types the current tables already use
  • Compare integer, UUID, time-ordered ID and natural keys in a short table
  • Benchmark insert speed for two candidate key types on a test table
  • Write and merge the decision record

Make it yours.

Install Database Schema Design, then tell Aurum the date you're aiming for. It checks whether the plan fits the weeks you actually have.

Free · Mac (Apple silicon) and Windows · v1.0.5 · Other platforms

Blueprints are general information to help you organise your own work. They are not medical, financial, legal or other professional advice. See the Terms.