Database Schema Reviews in English: Vocabulary for Data-Intensive Systems

Database schema reviews are a critical quality gate in any data-intensive system. A poorly reviewed schema can cause performance problems, migration failures, and backward compatibility breaks that are expensive to fix in production. For non-native English speakers, these reviews involve a specific technical vocabulary — one that is used consistently across code reviews, design documents, and incident post-mortems. This article covers the terms you need to participate confidently.

Key Vocabulary

Schema A schema is the formal structure of a database — the definition of tables, columns, types, constraints, and relationships. In a schema review, the team evaluates whether this structure will support the application’s needs efficiently and safely. “Before we merge this PR, I’d like to schedule a schema review — there are a few design decisions that could have significant performance implications at scale.”

Migration A database migration is a versioned change to the schema — adding a column, renaming a table, changing a data type. Migrations are applied in order and must be carefully managed to avoid data loss or downtime. “The migration adds a NOT NULL column to a table with 50 million rows — we need to discuss the deployment strategy to avoid locking the table.”

Backward compatibility A backward-compatible schema change is one that does not break existing application code. Dropping a column, renaming a column, or changing a data type in a breaking way are common sources of compatibility issues. “Adding a nullable column is backward-compatible — existing queries and inserts will continue to work without modification.”

Normalisation Normalisation is the process of organising a schema to reduce data redundancy and improve data integrity, typically by ensuring each piece of data is stored in only one place. “The current schema is denormalised for read performance — user data is duplicated across three tables, which creates consistency risks.”

Index An index is a data structure that improves the speed of data retrieval on a column or set of columns. Indexing decisions are a central part of schema reviews, as both missing and excessive indices have performance consequences. “There is no index on the created_at column, which will cause a full table scan for every date-range query — this will not scale.”

Cardinality Cardinality refers to the number of unique values in a column. High cardinality columns (like user IDs) are good candidates for indexing; low cardinality columns (like boolean flags) often are not. “Adding an index on the status column is unlikely to help — it has low cardinality with only three possible values.”

Foreign key constraint A foreign key constraint enforces referential integrity between tables — ensuring that a value in one table corresponds to a valid value in another. Removing or not adding foreign key constraints is a common source of data integrity bugs. “I’d recommend adding a foreign key constraint here — without it, we can orphan records in the orders table if a user is deleted.”

Zero-downtime migration A zero-downtime migration is a schema change strategy that avoids locking tables or causing service interruptions during deployment. Common techniques include adding nullable columns first, backfilling data, then adding constraints in a subsequent migration. “To achieve a zero-downtime migration, we will use the expand-contract pattern: add the new column, backfill it, then drop the old column in a separate deployment.”

Useful Phrases

• “I have a few concerns about the indexing strategy — can we walk through the expected query patterns before we finalise the schema?”
• “This migration is not backward-compatible — if we deploy the schema change before the application code, existing queries will fail.”
• “The normalisation here trades write simplicity for read complexity — I want to make sure we have discussed the query implications.”
• “Adding a NOT NULL constraint to an existing column is a destructive migration on a large table — we need a phased deployment plan.”
• “What is the rollback plan if this migration causes issues in production?”

Common Mistakes

Saying “modify” instead of “migrate” or “alter” “We need to modify the database” is underspecified. In schema review discussions, use “migrate” (for a versioned change managed through a migration tool) or “alter” (for the raw SQL operation). Being precise avoids confusion about the deployment strategy.

Ignoring the expand-contract pattern for breaking changes Non-native speakers who are newer to production database management sometimes propose applying breaking changes in a single migration. Always discuss phasing: add first (expand), then remove old usage from code, then remove the old schema element (contract). This is the standard pattern for zero-downtime migrations.

Confusing “nullable” and “optional” at the application layer A column can be nullable in the database but required by application logic. A column can be NOT NULL in the database but have a default value that makes it optional from the application’s perspective. These are distinct concepts — be explicit about which layer you are discussing in a schema review.

Schema reviews are one of the highest-leverage technical discussions a team can have. A vocabulary-confident participant can spot performance, integrity, and migration risks early — before they become production incidents.

Navigating Nuances: A Practical Guide for Non-Native Speakers

Let’s face it. Technical jargon can be tricky to grasp, especially when you’re learning a new language. Database schema reviews are full of terms that sound impressive but don’t always translate smoothly into everyday conversation. This section focuses on bridging that gap – providing context and practical examples to help non-native English speakers confidently participate in discussions about database design and evolution. The goal isn’t just to know the words, but to understand how they’re used and when to apply them. A key part of this is recognizing subtle differences in meaning; for example, “normalization” can be interpreted differently depending on the conversation – are we talking about 3NF or something more relaxed? Don’t be afraid to ask clarifying questions! It’s far better to admit you don’t understand than to make assumptions that could lead to misunderstandings. Remember, effective communication is built on shared understanding, and a willingness to learn from others. Focus on conveying your intent – what you mean to say – rather than obsessing over using the “perfect” technical term every time. Most importantly, be respectful of differing opinions; schema design is often highly contextual and there isn’t always a single “right” answer.

A common scenario arises when reviewing a pull request for database changes. Imagine receiving this comment in Slack: “This schema change introduces some potential performance issues. We should definitely consider adding an index on the customer_id field to optimize queries against that table.” The key here is understanding why the suggestion is made. The reviewer isn’t simply stating a fact; they’re identifying a possible problem and offering a solution—an indexing decision. Similarly, when writing the PR description, you might say: “This update introduces a new order_status column to improve reporting accuracy. We are also implementing an index on customer_id to enhance query performance for frequently accessed customer orders.” Notice how we’ve explained why the change is being made, not just what it is.

Another example: During a code review, you might hear someone discussing “backward compatibility.” This refers to ensuring that new database versions remain compatible with older applications – preventing breaking changes. It’s frequently linked to migration strategies. A developer might explain: “To maintain backward compatibility, we’ll use a schema evolution strategy that adds the new column as an optional field initially, allowing existing queries to continue working without modification.” This demonstrates foresight and planning.

Let’s look at a simple example of how you could use psql (PostgreSQL’s command-line tool) for a quick index creation:

CREATE INDEX idx_customer_id ON orders (customer_id);

This command creates an index named idx_customer_id on the customer_id column of the orders table. It’s a basic illustration, but it demonstrates how the vocabulary – indexing decisions – can be applied practically. The key is to understand what this command does and why it might be necessary based on your database design needs.