Normalization and ACID properties aren't academic trivia, they're the difference between a schema that survives five years of feature creep and one that collapses under its own joins. I put together everything I actually use when designing databases, including how Slowly Changing Dimensions save you from historical da...
I redesigned the candidate and job application schema for ITJobOpportunities three separate times before I landed on something I was happy with. Not because I didn't know normalization theory (25 years in, I could teach 3NF in my sleep), but because knowing the rules and knowing when to break them are two very different skills. Every time I over-normalized early, I paid for it later in joins. Every time I got lazy and denormalized too soon, I paid for it in bugs. This is the stuff nobody covers well. Normalization, ACID guarantees, and slowly changing dimensions aren't three separate topics you learn once and forget. They're three lenses on the same core problem: how do you store data so it stays correct and stays fast. Here's how I actually think about each one, with real examples and real SQL from building a hiring platform from scratch. Normalization: Theory Meets the Real World Normal forms exist to kill redundancy and prevent update anomalies. First normal form says no repeating groups. Second says every non-key attribute depends on the whole key, not just part of it. Third says non-key attributes depend on nothing but the key, not on some other non-key column. There's a Boyce-Codd form, and technically forms four through six exist for multivalued and join dependencies, but I've never once needed them in production. Not once in 25 years. If you're reaching for 5NF or 6NF outside an academic paper, something's probably gone sideways in your modeling process. 3NF is where I live for almost everything. It's the sweet spot: enough structure to prevent the "update the email in fifteen places" nightmare, without turning every query into a six-table join fest. Here's a concrete example of a 2NF violation, because it's the one people skip past too fast. Say you have a table tracking which skills a job requires, and you store the company name right there on the join row because it's "convenient" for reporting: create table job_skill ( job_id bigint not null, skill_id bigint not null, company_name varchar(255) not null, required_level varchar(50), primary key (job_id, skill_id) ); company_name depends only on job_id , not on the composite key (job_id, skill_id) . That's a partial dependency, a textbook 2NF violation. The tell is simple: if you insert a new skill for an existing job, you're re-typing the company name you already have somewhere else. Change the company's name and you now have to update it across every skill row for every job that company posted. The fix is pulling company_name back to the job table where it actually belongs, and letting job_skill reference job_id for anything job-level: create table job_skill ( job_id bigint not null references job(id), skill_id bigint not null references skill(id), required_level varchar(50), primary key (job_id, skill_id) ); Now here's the 3NF example from the job posting schema itself. Early on, I had a job_applications table that stored the candidate's name, email, and phone number directly on every application row. Seemed fine at first. Then a candidate applied to three different featured jobs in the same week, updated their phone number after switching carriers, and now two of the three application rows had stale data. Classic update anomaly, straight out of a textbook, except this time it was my textbook and my bug ticket. The fix was pulling candidate identity into its own table and referencing it by foreign key: create table candidate ( id bigint generated always as identity primary key, email varchar(255) not null unique, full_name varchar(255) not null, phone varchar(50) ); create table job_application ( id bigint generated always as identity primary key, candidate_id bigint not null references candidate(id), job_id bigint not null references job(id), applied_at timestamptz not null default now(), relevance_score integer ); One update to the candidate record, and every application reflects it. That's the whole point of 3NF: a single source of truth for a fact, referenced everywhere it's needed. Where Normalization Bites You The tradeoffs are real, and I don't think people talk about them honestly enough. Joins get expensive. Once you've normalized candidates, jobs, companies, skills, and applications into separate tables, a single "show me this application with everything a recruiter needs" query can touch five or six tables. On Postgres with good indexes this is usually fine. On a table with tens of millions of rows and a bad query plan, it is not fine. I've had to explain that difference to a junior engineer more than once, usually by pulling up EXPLAIN ANALYZE and pointing at the sequential scan nobody expected. You write more DDL, more migrations, more Liquibase changesets. Every relationship is another table, another foreign key, another thing that can break during a schema migration. Reporting queries suffer. Analysts and dashboards want flat, wide tables. Normalized schemas want narrow, deep ones. That tension is exactly why data warehouses exist as a separate concern from OLTP systems. The join cost problem is fixable, but only if you index the right things. A foreign key column without an index is one of the most common mistakes I still see in code review: create index idx_job_application_candidate_id on job_application (candidate_id); create index idx_job_application_job_id on job_application (job_id); Without those two indexes, a query joining job_application to candidate and job forces Postgres to scan the whole applications table for every candidate lookup once volume grows. I've watched a dashboard query go from twelve seconds to forty milliseconds just from adding the missing index on a foreign key that everyone assumed was already indexed because it "had a constraint on it." A foreign key constraint enforces referential integrity. It does not automatically create an index on the referencing column. That's a Postgres detail that trips up people who assume MySQL behavior everywhere. I've settled into a rule of thumb: normalize your write path, and if you need a flat read path, build a materialized view or a read replica with a denormalized projection. Don't try to make one schema serve both jobs well. It won't. create materialized view application_summary as select ja.id as application_id, c.full_name, c.email, j.title as job_title, j.company_name, ja.applied_at, ja.relevance_score from job_application ja join candidate c on c.id = ja.candidate_id join job j on j.id = ja.job_id; create unique index on application_summary (application_id); refresh materialized view concurrently application_summary; That unique index matters. refresh materialized view concurrently requires one, otherwise it falls back to locking the view for reads during refresh, which is exactly what you don't want on a dashboard people are actively using. This is basically the shape of the ATS admin console's dashboard query pattern under the hood, minus a bunch of filtering logic. Recruiters want a flat table of applicants. Under the covers, the data lives normalized. Everyone's happy. ACID: The Part People Take for Granted Until It Breaks Atomicity, consistency, isolation, durability. Every relational database vendor claims to support all four, and mostly they do, but the details matter way more than the acronym suggests. Atomicity means a transaction either fully commits or fully rolls back. No half-finished states. This one bit me early in the Easy Apply flow. When a candidate submits a resume, the backend has to create or update the candidate record, create the job application row, kick off a skills extraction job, and fire a notification. If the notification service throws an exception, do you want the candidate record rolled back too? No, obviously not. So the notification call happens outside the transaction boundary entirely, fired after commit, usually through an event or an async job. Getting that boundary wrong is one of the most common mistakes I see in Sprin...