DataslopeWhy Schemas Change
Understand why requirements force schemas to evolve and why those changes become risky after data and code depend on them.
A schema can be well designed and still need to change.
That is not failure. It is what happens when software learns more about the real world.
The design challenge is to understand what kinds of changes happen and why they carry risk once the database is in use.
Requirements evolve
At the start, a simple task tracker might store only tasks:
Then users ask for projects, owners, due dates, labels, and audit history. Each new requirement asks the schema to represent a new fact or rule.
A relational schema is a living model. As the product meaning changes, the model must change too.
Common kinds of change
Schema changes usually fall into a few patterns.
New attributes
A new attribute adds a fact to an existing entity. For example,
tasks.due_on says a task may have a due date.
This is often the safest change when the new column is nullable or has a sensible default.
New entities
A new entity appears when a concept needs its own identity and facts.
If tasks now belong to projects, projects probably deserves a table.
New relationships
Sometimes the entities already exist, but the connection changes. A single owner might become a team of assignees. That turns a one-to-many relationship into many-to-many.
Splitting and merging tables
A table may become too broad. For example, customer billing details may
move from customers into billing_profiles when they gain their own
rules.
Merging is less common, but it can happen when two tables turn out to represent the same concept.
Tightening constraints
Early in a product, a column may allow nulls because old data is messy. Later, the team may learn that the fact is always required.
Which schema change is usually safest to introduce first?
Dropping a table that application code still reads.
Renaming every primary key at once.
Adding a nullable column for a new optional fact.
Replacing a many-to-many table with a comma-separated text column.
Why change becomes risky
Once a database has real data and real application code, the schema is not just a diagram. Other things depend on it.
Changing a table can break inserts, joins, reports, imports, and user interfaces. A good change plan considers all of those dependencies.
A small evolution example
Version 1 stores tasks with no project table.
Version 2 adds projects and links tasks to them.
The design meaning changed: a task can now be grouped under a project. That might affect screens, reports, and validation rules.
This example adds structure without destroying the existing task rows.
A later migration could make project_id required after every task has
a project.
Check your understanding
Why do schemas change even when the original design was good?
Good designs never need to change.
The schema models requirements, and requirements evolve.
PostgreSQL randomly changes table definitions.
Tables can store only a few rows.
What makes destructive changes risky?
They are always impossible in PostgreSQL.
Existing data, code, and reports may depend on the old shape.
They only affect diagrams, not data.
They automatically create better normalization.
When might a new table be better than adding more columns to an old table?
When the new concept has its own identity, rules, and relationships.
When the old table has a primary key.
When SQL queries use SELECT.
When every row has exactly the same value.