Normalization, Denormalization, and ORM vs Stored Procedures — DB Design (DB Series 3/4)

Why normalize: three anomalies that duplication causes

Start with one unnormalized table. All the course enrollment data goes in a single table.

The fact that Prof. Park teaches Intro to DB is stored once for every student in the course. That duplication causes three kinds of trouble.

• Update anomaly: when a course title changes, every row for that course has to change. Miss one and the data contradicts itself.
• Insert anomaly: a new course with no students yet has no way in. student_id is part of the key, so it can’t be left empty.
• Delete anomaly: when the last student drops the course, the course information disappears with them.

Normalization breaks that duplication apart along functional dependencies so that each fact is stored in exactly one place. You split tables along “if you know X, Y is determined (X → Y).”

First, second, and third normal form

1NF: one value per cell

Every column holds an atomic value. The moment you put several values in one cell, like phone = "010-1111, 010-2222", you’ve broken it. You can’t search or index that cell properly, so repeated values become rows or move to their own table.

2NF: remove columns that hang on part of the key

The key of the table above is the composite (student_id, course_id). But the student’s name is determined by student_id alone, and the title and professor by course_id alone. That’s a partial dependency. Columns that depend on only part of the key move to their own tables.

student    (student_id, name)
course     (course_id, title, professor)
enrollment (student_id, course_id, grade)  -- only grade depends on the whole key

3NF: break chains between non-key columns

Say you add the professor’s department next to the professor in course. Now there’s a transitive dependency: course_id → professor → department. When a professor changes departments, every course row they teach has to change, and the update anomaly is back. When a non-key column determines another non-key column, split that out too.

course    (course_id, title, professor_id)
professor (professor_id, name, department)

Denormalization: buying duplication back, knowingly

Normalization sends its bill at read time. The more you split tables, the more JOINs you need. Rendering one order list screen takes a five-table join, and when that becomes the bottleneck in a busy service, that’s when denormalization comes out.

• Duplicated columns: copy the product name into the order. Strictly, the product’s name and price at order time are a different fact from its current values, so copying them is often the right model anyway.
• Aggregate columns: keep a comment_count on each post so you can drop the COUNT and the join.
• Merged and summary tables: combine tables that are always read together, or keep a separate table for statistics.

The price is exactly what normalization was protecting you from. With duplication, every change has to touch several places, and keeping them in sync is now the application’s job. For an aggregate column, it looks like this.

START TRANSACTION;
INSERT INTO comment (post_id, body) VALUES (42, '...');
UPDATE post SET comment_count = comment_count + 1 WHERE id = 42;
COMMIT;  -- if only one of them lands, the count drifts

Both statements go in one transaction for the atomicity reasons from part two. That’s why order matters. Design normalized first, then denormalize only where you’ve measured a bottleneck. A table lumped together from day one “to make reads easy” isn’t denormalized. It’s just not normalized.

ORM vs stored procedures: where should logic live?

The last design question. Business logic can live in application code (through an ORM) or inside the database as stored procedures.

There was a reason procedure-heavy development was common in enterprise and banking systems. When networks were slow and application servers were weak, doing everything in the database was faster. Today it’s the other way around. Application servers are cheap to scale horizontally, and databases are expensive. So the default is to keep logic in the application.

Procedures still win in some cases.

• Batch jobs that read millions of rows, transform them, and write them back. Hauling the data out to the application is waste in itself.
• Cases where many statements belong in one transaction and the number of round trips decides performance.
• Legacy environments where several systems share one database and rules have to be enforced at the database level.

Wrap-up

• Normalization decomposes along functional dependencies so each fact lives in one place, preventing the three anomalies.
• 2NF removes partial dependencies, 3NF removes transitive dependencies. The whole key, and nothing but the key.
• Denormalize only for measured bottlenecks, on purpose, and own the syncing.
• Keep logic in the application by default, and use procedures only for bulk batches and round-trip bottlenecks.