When your code feels hard, 9 times out of 10, the problem is with your data model.
I'll never forget the interview question that wrecked my brain and felt almost unsolvable with my approach (set matching), then a friend showed me it's a graph problem and the solution was obvious – a basic for loop 🤯
And when this happens in production code, it's much harder to spot. We re-learned this lesson a few weeks ago.
Data modeling, it matters
Our system has an Appointment model. It connects a time and place with a patient, healthcare provider, and a bunch of meta data.
The full model has almost 20 columns/properties. Conceptually overloaded, needs cleaning up, but a convenient place to add onto. Much of our system relies on this.
Appointments have cancellation policies – if you cancel less than 48 hours before the appointment, there's a fee you have to pay. This keeps the system fair.
Until a few weeks ago, the policy was a hardcoded constant in our code. No need to get fancy because it's always the same.
But a new concept arose: We want to be less strict with certain appointments and allow cancelling up to 24 hours before. Time to move cancellation policies into the database!
How would you model that? 🤔
We tried the obvious first
The obvious answer was our first try – every appointment has a cancellation policy.
This works. You get a new cancellation policy table that points to your appointments and defines their policy. You can read the data with a SQL JOIN or using an ORM.
Your table will have a bunch of rows that all look the same, but that's okay. Disk space is cheap.
But how do you guarantee that every appointment has a cancellation policy? It needs one or your code won't work.
We googled and found hack after hack. You can write a
CHECK constraint with a custom PgSQL function. Or a database trigger that checks and throws an error. Or write a bunch of application code to check by hand and lose the guarantee, if someone adds data manually.
This is too hard, something's wrong
The lack of easy solutions and sparse search results could mean only one thing: We were on the wrong track. Something's wrong.
We looked at our data model again. Every appointment has a cancellation policy, obvious ...
... and then it clicked! Every appointment belongs to a cancellation policy 💡
With that tiny arrow change we got:
- unique rows in our table, many rows turned into 2
- easy consistency guarantees
- ability to operationalize policies with a UI dropdown
Semantically turning the table into "type of cancellation policy" means you need fewer rows, can use it to populate UI dropdowns, and need to change just 1 row in your database to update policies.
Best part: Guaranteeing every appointment has a policy is as easy as
policy_id NOT NULL REFERENCES cancellation_policy. Your database handles the rest 🤘
One tiny little change made everything easier. That's why I say forget the framework wars, focus on the fundamentals.
And the best way to learn is to build lots of things. Books help but experience wins.
PS: this falls under the umbrella of tacit knowledge. Even if you read all the theory about relational data models it won't click until you get your hands dirty.
Want to become a Fullstack Web expert?
Learning from tutorials is great! You follow some steps, learn a smol lesson, and feel like you got this. Then you go into an interview, get a question from the boss, or encounter a new situation and o-oh.
Shit, how does this work again? 😅
That's the problem with tutorials. They're not how the world works. Real software is a mess. A best-effort pile of duct tape and chewing gum. You need deep understanding, not recipes.
Leave your email and get the Fullstack Web Essays series - a series of curated essays and experiments on modern Fullstack Web development. Lessons learned from practice building production software.
Curated Fullstack Web Essays
Get a series of curated essays on Fullstack Web development. Lessons and insights from building software for production. No bullshit.
Senior Mindset Book
Get promoted, earn a bigger salary, work for top companiesLearn more
Have a burning question that you think I can answer? Hit me up on twitter and I'll do my best.
Who am I and who do I help? I'm Swizec Teller and I turn coders into engineers with "Raw and honest from the heart!" writing. No bullshit. Real insights into the career and skills of a modern software engineer.
Want to become a true senior engineer? Take ownership, have autonomy, and be a force multiplier on your team. The Senior Engineer Mindset ebook can help 👉 swizec.com/senior-mindset. These are the shifts in mindset that unlocked my career.
Curious about Serverless and the modern backend? Check out Serverless Handbook, for frontend engineers 👉 ServerlessHandbook.dev
Want to Stop copy pasting D3 examples and create data visualizations of your own? Learn how to build scalable dataviz React components your whole team can understand with React for Data Visualization
Did someone amazing share this letter with you? Wonderful! You can sign up for my weekly letters for software engineers on their path to greatness, here: swizec.com/blog
By the way, just in case no one has told you it yet today: I love and appreciate you for who you are ❤️