Single Source of Truth

Single source of truth is one of the most important principles in software engineering. In web applications, that source of truth is the database and getting the design wrong is expensive to fix later.

To keep things simple, I will mainly put my focus on SQL databases, using PostgreSQL for my examples. Other engines like Oracle have slightly different syntax, but the principles carry over. To keep the article short, we will only cover these 2 today:

Why PostgreSQL

PostgreSQL is open source and free to use, with no licensing costs or vendor lock-in. It is battle-tested in production at companies like Apple, Instagram, and Reddit, and consistently ranks among the most loved databases in developer surveys.

Performance is excellent out of the box, the query planner is mature, indexing options are rich (B-Tree, GIN, GiST, BRIN), and features like parallel query execution and JIT compilation keep it competitive with commercial offerings. It also handles workloads beyond plain relational data: JSONB for document storage, full-text search, and extensions like PostGIS for geospatial queries.

For most web applications, PostgreSQL is a safe default and the principles in the rest of this article apply regardless of which engine you pick.

Constraints

Introduction

Database constraints control what data your tables are allowed to store.

For the database to be a reliable source of truth, validation must start at the database level. A common question is whether to validate data at the frontend or backend, the truth is both, plus the database. Multiple teams often share the same database, and enforcing constraints at the database level ensures data integrity regardless of which system writes to it.

That said, there are scenarios where constraints should not be applied. A data lake is a centralized repository that stores raw, unprocessed data exactly as it arrives from the source. Applying constraints here would cause ingestion to fail whenever the source data is messy or doesn't match an expected schema which defeats the purpose. Data lakes are designed to accept everything first and ask questions later.

Cleaning and structuring happens downstream, in a data warehouse. A separate pipeline reads from the data lake, transforms the data into a consistent shape, and loads it into the warehouse where it can be safely queried. If the source changes its schema unexpectedly, this pipeline should fail, that failure is intentional and acts as an early warning. The data warehouse is where constraints belong, because it is the system of record for analysis and reporting.

Application

Choosing the right data type is the first line of defence. Before writing a single constraint, ask yourself:

For example, a username should use a character-varying type capped at a sensible length, since it is never null and must be unique:

CREATE TABLE users(id serial primary key, username varchar(50) not null unique);

These decisions compound. A well-typed schema catches invalid data before it reaches any constraint, and makes the constraints you do write simpler and more reliable.

Checks

Beyond built-in data types, you can attach custom constraints. To store a product price — which must be non-negative with two decimal places — combine the

numeric

CHECK

CREATE TABLE products (
  id integer primary key,
  name varchar(50),
  price numeric(5,2) CHECK (price >= 0)
);

numeric(5,2)

Primary Keys

Primary keys uniquely identify each row. PostgreSQL automatically creates a B-Tree index on primary key columns for fast lookups.

Foreign Keys

Foreign keys reference a primary key in another table. You control what happens when that referenced row is deleted:

CREATE TABLE order_items (
    product_id integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_id, order_id)
);

Not Null

The NOT NULL constraint rejects null values. Note that null is not the same as an empty string. To reject both, pair

NOT NULL

CREATE TABLE users (
  id serial primary key,
  username VARCHAR NOT NULL CONSTRAINT non_empty CHECK(length(username) > 0)
)

Unique

The UNIQUE constraint ensures every value in a column is distinct. It can also span multiple columns. Consider a favorites table where users save listings — a composite unique constraint on

user_id

flat_id

class CreateFavorites < ActiveRecord::Migration[6.1]
  def change
    create_table :favorites do |t|
      t.references :user, null: false, foreign_key: true, index: true
      t.references :flat, null: false, foreign_key: true, index: true
      t.timestamps
    end
    add_index :favorites, [:user_id, :flat_id], unique: true
  end
end

Database Normalization

Normalization is a design technique that eliminates redundancy and prevents data anomalies.

With column-level constraints covered, we turn to table structure. We'll work through the first three normal forms plus Boyce-Codd Normal Form (BCNF) — typically enough depth for most production schemas and technical assessments. Higher normal forms exist but are beyond our scope here.

For simplicity, assume columns without the word ID carry all information for that domain. For example a

Student

1st Normal Form

Consider a student grade report stored in one table:

Student_Grade_Report (StudentId, Student, Major, Advisor, [Course, Grade])

One student takes many courses — so the

[Course, Grade]

Student (StudentId, Student, Major, Advisor)
StudentCourse (StudentId, Course, Grade)

This isn't just tidiness. With the original design, two concurrent read-then-write operations can produce a lost update:

Row1 = student, [course1, course2]
User1 and User2 both read Row1
User1 adds course3 → Row1 becomes [course1, course2, course3]
User2 removes course2 → Row1 becomes [course1, course3]
Result: course3 silently lost

After splitting, updates to each table happen independently and safely.

2nd Normal Form

Two domains exist here: students and courses. Their relationship — which student takes which course, and the resulting grade — belongs in its own table:

Student (StudentId, Student, Major, Advisor)
StudentCourse (StudentId, CourseId, Grade)
Course (CourseId, Course)

Without this split, the same course name repeats across every student record that takes it. Updating the course name means touching every one of those rows, which is an expensive operation at scale. With the normalized design, an admin can update course information while a professor enters grades, and neither transaction touches the other.

3rd Normal Form

Examine the

Student

Student (StudentId, Student)
StudentMajor (StudentId, Major, Advisor)
StudentCourse (StudentId, CourseId, Grade)
Course (CourseId, Course)

Boyce-Codd Normal Form

BCNF is a stricter variant of 3rd Normal Form, and its application depends on business rules. Given these rules for the

StudentMajor

StudentMajor (StudentId, Advisor, Major)

This schema cannot enforce rule 4. There is nothing stopping an advisor from being associated with multiple majors. To make the rule enforceable, decompose further:

StudentAdvisor (StudentId, AdvisorId)
AdvisorMajor (AdvisorId, Advisor, MajorId)
Major (MajorId, Major)

Now a student has an advisor, an advisor belongs to exactly one major, and a major can have many advisors. The constraint is structural — the database enforces it automatically.

Migrations

Constraints and normalization only matter if they actually reach the database, and the way to get them there is through a migration framework, not by running SQL against production by hand. Migrations are versioned, reviewable files checked into the repository alongside application code. Every schema change has an author, a commit, and a rollback path.

Running ad-hoc SQL against a shared database is how environments drift. Staging stops matching production, a column exists on one replica but not another, and nobody can reproduce the bug. A migration framework solves this by tracking which changes have been applied and replaying any that haven't, in order, on every environment.

The tool varies by stack, the discipline does not:

Go — golang-migrate uses paired up/down SQL files:

-- 000001_create_users.up.sql
CREATE TABLE users (
  id serial PRIMARY KEY,
  username varchar(50) NOT NULL UNIQUE
);

-- 000001_create_users.down.sql
DROP TABLE users;

migrate -path ./migrations -database "$DATABASE_URL" up

TypeScript — Prisma Migrate generates SQL from a schema file:

model User {
  id       Int    @id @default(autoincrement())
  username String @unique @db.VarChar(50)
}

npx prisma migrate dev --name create_users
npx prisma migrate deploy

Knex and TypeORM are common alternatives if you prefer writing migrations directly.

Python — Alembic (paired with SQLAlchemy) generates revisions from model changes:

def upgrade():
    op.create_table(
        "users",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("username", sa.String(50), nullable=False, unique=True),
    )

def downgrade():
    op.drop_table("users")

alembic upgrade head

This should be the default for any engineering team. Direct SQL on production is a break-glass action, not a workflow. If a change is worth making, it is worth a migration file, a code review, and a deploy pipeline that runs it the same way in every environment.

AI and the Database

AI hasn't changed the fundamentals of database design. Constraints, normalization, and migrations all predate the current wave of LLM tooling and still apply unchanged. What has changed is how easy it is to point an agent at a database and let it run queries on your behalf and that convenience deserves a careful look at access rights.

Treat database credentials handed to an AI agent like credentials handed to an unknown intern with no on-call experience. Read-only access is generally fine for exploration, debugging, and analytics, the worst case is a slow query. Read-write access is dangerous and should be avoided outside of isolated development environments. An LLM that hallucinates a column name in a

SELECT

UPDATE

DELETE

A few practical guardrails:

The discipline is the same one that applies to any other system credential — the AI just makes it easier to forget.

Conclusion

The database is the single source of truth for your users' data. Constraints keep individual values valid, normalization keeps the structure consistent, and migrations keep every environment aligned with the schema in version control. The three work together, strong constraints in a drifted schema are worthless, and a perfectly normalized design that only lives on one engineer's laptop helps no one.

Schema problems compound quietly. A missing constraint, a denormalized table, or an undocumented hotfix applied straight to production rarely breaks anything on the day it ships. It breaks months later, when the application is serving real users and the rewrite is both risky and expensive. Some companies employ a Database Administrator to review schemas before they reach production, a sign of how seriously good teams take this work.