You've heard that array or list columns in a database are bad. But why? Yesterday it clicked for me 👇
2 ways to model a relationship
Let's say you have two concepts to model in a database:
- Posts
- Reactions
Posts can be anything. Blogs, tweets, newsletters, doesn't matter. There's text and metadata. Posts are written once and rarely if ever change. They get read a lot.
Reactions are comments, likes, beard strokes, whatever you like. You need to know the count, but also keep them unique. Reactions are written once and never change. But they can happen on very old posts.
How would you model this?
The array or list approach
One way is to smoosh this together in a table/object NoSQL style. Every modern database will let you do this and it's going to work.
You can use an array() column like was popular in the 2000's or a jsonb column with a list. That's more popular today.
The benefit is that this is obvious and easy to think about: Posts have an array of reactions. You can add logic in your application code to keep them unique and you're on the hook for maintaining shape-consistency between elements.
The relational approach
Another standard approach is to model this as a relationship between two tables in the database.
Reactions have a post_ID column that points to the post they belong to. You have to use a join to read these tables together.
This approach is more cumbersome to use, but you can let the database enforce uniqueness and ensure all reactions have a consistent shape. You have less work to do in application code.
Writes are where the magic happens
Already the relational model is looking good. Let the database do more work while you focus on the good stuff. But writes are where the relational model truly shines.
Think about how this data is laid out in storage.
Storage is linear. All your data has to fit one after another.
On the left you have the array approach. Every post contains its data and reactions. When one post is over, the next one begins.
On the right you have the relational approach. Posts are small with just their data. Reactions are small too. Each table occupies a distinct area in storage.
What happens when you add a reaction?
Now what happens when you add a reaction. On a very old post?
With the arrays approach, your database has to make room for the new reaction. Every post in the entire table has to move out of the way, then it can add a new reaction. The more data you add, the more everything has to move.
This makes writes slow. You're adding 2 integers, but moving and rewriting gigabytes of data. Plus updating any indexes.
Edit: this is wrong. Explanation here 👉 I was wrong about databases
With the relational approach, your database appends a reaction at the end of the reactions table. Nothing else needs to move.
This makes writes fast. You're adding 2 integers, and writing only those two integers and making a quick update to indexes.
Details like this make the difference between an app that scales and an app that hits a wall.
Cheers,
~Swizec
Continue reading about *Why* the relational data model is so good
Semantically similar articles hand-picked by GPT-4
- I was wrong about databases
- Why SQL is Forever
- Notes on A Relational Model of Data for Large Shared Data Banks
- How better data modeling fixes your code
- Why SQL is Forever followup
Learned something new?
Read more Software Engineering Lessons from Production
I write articles with real insight into the career and skills of a modern software engineer. "Raw and honest from the heart!" as one reader described them. Fueled by lessons learned over 20 years of building production code for side-projects, small businesses, and hyper growth startups. Both successful and not.
Subscribe below 👇
Software Engineering Lessons from Production
Join Swizec's Newsletter and get insightful emails 💌 on mindsets, tactics, and technical skills for your career. Real lessons from building production software. No bullshit.
"Man, love your simple writing! Yours is the only newsletter I open and only blog that I give a fuck to read & scroll till the end. And wow always take away lessons with me. Inspiring! And very relatable. 👌"
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
Want to get my best emails on JavaScript, React, Serverless, Fullstack Web, or Indie Hacking? Check out swizec.com/collections
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
Want to brush up on your modern JavaScript syntax? Check out my interactive cheatsheet: es6cheatsheet.com
By the way, just in case no one has told you it yet today: I love and appreciate you for who you are ❤️