Working 5 Months Next Quarter vs. Discussing for 5 Hours Now
3 Things You Must Never Forget When Working with DBs
The Grunt Work We've All Done at Least Once
Who here has ever dug themselves into a hole while merging tables or moving data around in Excel or Notion?! 🙋
I'll confess first: I've experienced this countless times. When the team changes a report template and you have to migrate the old content, or when you're using the same document structure for different clients but with different content, and fixing one thing means you have to manually update everywhere else... Depending on the tools you use, this might be rare, but you've still probably gone through this at least once.
In mobile/web services, there's an equivalent to Excel, and it's exactly the Database (DB). It's the place where tons of data about users or the service itself keeps piling up. Just like with Excel, there are quite frequent cases in DBs where you have to add values or completely change them into a new structure.
How can we do less of this grunt work when this happens?
The Lesson Left by 280 Hours
In the Wake Up squad, we've been preparing a "Synchronization" feature that saves users' alarm and sleep mode usage history to the server. You're probably familiar with names like cloud save or backup, and the goal of this feature is to allow users to pull in their past history exactly as is, even if they reinstall the app or change devices.
To prepare this sync feature, we ran a massive update on our in-app DB during the first three sprints of the new year. Metaphorically speaking, you can understand it as a job where we took the Excel file we'd been using all along and migrated absolutely everything over to a new spreadsheet.
H.O.W.E.V.E.R! Just this prep process took a total of about 280 hours across both Android and iOS platforms. If we convert this into the resource of a single engineer, it amounted to roughly 9 sprints, or 5 months of time.
Because the task was so big, there were many discussions about how to change the DB and why we had to do it. But what was even harder than the discussion itself was figuring out the starting point: "Where and how do we begin this discussion?" Looking back at past discussions after all the work was done, they boiled down perfectly into a 3-point checklist.
Check the product's OOO
Imagine a situation that is OOO
Check the OOO where the product is used
I'll introduce good questions to start a discussion with, along with vivid, fresh-out-of-the-oven examples for each. If you're designing, changing, or trying to improve a DB for the first time, please just remember these three things. Racking your brains and discussing for a brief 5 hours now can save you 5 months in the future.
First, Check the Product's OOO
👀 Hint: What will our product look like in 1 year?
To help you guess the answer, I'll first show you an example we experienced during this project. 😉
The case study for the first checklist came right out of the alarm history side. Previously, the record of a single alarm ringing and getting dismissed was being recorded in the same row. When an alarm rang, a new row was created recording the ring time, and then that row was updated accordingly as it was snoozed or dismissed.
But, during a discussion with our engineers, this question came up:
"What other values might be added later on?"
The reason this question came up was because, if things stayed as they were, we would have to do a DB Migration AGAIN! when we wanted to add new data to the alarm history later. For example, if we decided to record the exact timestamp of a snooze—which is currently aggregated only by count—we'd have to add a new column to the current table.
But what if after that, we suddenly wanted to save the success or failure timing of a mission...? We'd have to do a DB Migration yet again.
Even so, it was unreasonable to lock in the data that would be added in the future right now and change the table structure in advance. A product evolves organically while communicating with users, so you can never be 100% sure about the future.
Okay! Then what we need right now is two things.
Not a 'table with a structure predefined for future needs', but a 'flexible table that can easily respond to future needs'.
Instead, we must maintain bundling the process of a single alarm ringing to being dismissed as one set, just like the current structure.
We designed a new table structure tailored to these requirements. We put alarm-related records on an equal footing and designed it so that if they occurred from the same alarm, they would share the same "Ring ID" value. Like this!
Shall we reveal the answer around now? The answer is exactly "Check the product's [ Roadmap ]".
Whether it's a product/service or a table you're personally managing, it won't stay exactly as it was when first created. There will be future strategies and directions. Since changing a DB structure and migrating the old contents identically requires a lot of resources, it's crucial to check where you're trying to go forward and design a structure that can handle that. If you check the product's roadmap—that is, what features are planned—you can prevent yourself from slapping your forehead going "Oops!!" and regretting it later.
☑️ We might end up adding {such-and-such feature} in the future; will there be any issues?
☑️ On the flip side, we might just drop {this feature}; is it okay if this column disappears?
☑️ Is there a method that will cause minimal disruption even if the roadmap changes later?
🍯 Pro Tip: If your future roadmap isn't clear, take a look around at what data similar services have!
Second, Let's Imagine a Situation That Is OOO!
👀 Hint: What if you stacked 1,000 of this data a day, every day for 10 years?
The second case came from the sleep side. It was about the sleep stage and snoring data that gets saved when using sleep mode. Previously, the current time and sleep stage were being recorded every single minute.
The spark for this discussion came up while calculating server costs. Because sleep data piled up every minute, the size was small for now since the feature hadn't been launched long ago, but if it went on like this, it could become a massive scale. For instance, if one user uses sleep mode for 8 hours every day, and 10 years go by just like that? That's 1.8 million rows. What if all 2.3 million DAU use it this much...? Dizzying, right?
The problem here wasn't simply "there's too much data!". The real problem was that if structured differently, the same data could be contained in a smaller volume—meaning, there could be waste in the current data. Because if the sleep stage or snoring classification value remains the same over consecutive times, it can be represented with a single row.
Of course, there was a purpose behind implementing it this way. Since the sleep-related feature was hot off the press, we had saved the raw data exactly as is in order to adjust the processing standards for it. Fortunately, at this point, those standards had stabilized to some degree, making it safe to save in a processed state rather than the raw original.
Thus, after discussions with the Sleep squad, we ended up changing the structure of the sleep stage and snoring tables. The 6 rows from the previous example screen get reduced to just 3 rows. If the sleep stage changes every 10 minutes, it reduces to 1/10; if it changes every 30 minutes, it gets reduced to 1/30!
The answer to the second checklist is "Let's imagine an [ Extreme ] situation!". Even if there seems to be no problem in a normal situation, if you assume an extreme amount and an extreme duration, you'll be able to see where the current inefficiencies lie. If you maximize the efficiency of your current structure by imagining these extreme situations, problems that arise during the process of maintaining the DB structure going forward will decrease, ultimately lowering overall costs.
A list of questions for imagining extreme situations!
☑️ What if 10,000 users use this feature 1,000 times a day for 10 straight years?
☑️ What is the purpose of the data we're currently saving? Is it strictly necessary?
☑️ Is there anything among the data we're currently saving that can be consolidated into one?
Third, Check the OOO Where the Product Is Used
👀Hint: What if you expand to a new OS? What if you connect web and app?
Up until now, Alarmy has evolved centering more on in-client operations rather than the server. This was because the very act of ringing an alarm required tight communication with the OS (Operating System). Because of that, while core policies were shared, detailed logic was optimized separately for Android and iOS operating systems.
The third case was born out of this background. It was precisely the difference in naming per OS for the identical feature: "mission."
Until now, it didn't matter at all if the DB names used by both OSs were different. Even at this current moment of deploying the sync feature, it's still not an immediate issue because both platforms have different login methods attached to them. But! What if one day we slap Apple Login onto Android and Google Login onto iPhone? Then we would need to be able to read data uploaded from an Android device on an iPhone.
If things stay like this, when features get developed later, we'd have to run Migration jobs on past data yet again. So we decided to completely unify the name values while we were at it. Almost no extra work cost was added, after all.
What name values did we unify, you ask? For example, the value referring to the "Memory mission" was previously different: memory on Android and memorygame on iOS. This difference happened because when the feature was first built, it was launched under the name 'Memory Game'. In this task, we went ahead and corrected it on the iOS side to the name memory.
Now then... ironically, after deploying the DB Migration task to users, we came to realize deeply just how important this unification work was. During the first week of deployment, bug reports saying "The mission screen isn't showing up" started pouring in. I got a sudden chill down my spine. Investigating the bug reports, we found they were occurring for users who used the Memory mission, and the root cause was due to changing the variable for the Memory mission name during the DB Migration process. That name wasn't just being used for saving; it was also being used to pop up the alarm dismiss screen. We figured out the cause and completed a hotfix update in a single day, but it was a dizzying moment.
Since a bug happened, we ran a retrospective. How could we do this better next time? #1 was testing more thoroughly. But the answer to "Why did we even have to change this value this time in the first place?" was because of consistency across OSs. If it had been consistent to begin with, this wouldn't have happened, and conversely, if there were values with different names across the two OSs right now, it was something that could happen in the future too. So rather, action item #0 became: when building a new feature, use the exact same name for values saved in the DB across both OSs!
The answer to the third checklist is "Check the [ Platform ] where the product is used". Beyond just Android and iOS, there are more operating systems we can use than you'd think, including OSs by manufacturer, web/app compatibility, and Watch/Pad OSs. When a service is small, changing a specific value that goes into the DB and testing it might not be very difficult. But as a product grows and complexity increases, it gets harder and harder to be 100% certain about when and where this value we're about to change will be used. It means we have to invest separate time researching existing policies, and even if we research well, it's tough to test everything perfectly.
The best thing is syncing the values before deployment. Discussing it for 5 minutes now can save you 5 hours later figuring out policies, debugging, and fixing.
The checklist here is very simple.
☑️ What platforms do/will we support? Are we using the same formats and values across multiple platforms?
🍯 Pro Tip: If you establish principles for the column names and value names to be used in the Database, the discussion time for name unification will get much shorter, right?
Let's Remember Just 3 Things
Alright, I've told you all 3 things to consider when handling a database. If we express each as a single keyword, it looks like this.
Check product direction → Scalability
Imagine extreme situations → Sustainability
Check platforms where the product is used → Compatibility
All three of these point toward the purpose of product growth. Ensuring that as time goes by, the product grows—meaning, the curve moves upward and to the right. The goal is to remove bottlenecks so that curve can climb as steeply as possible.
Therefore, if someone asks, "Why is a discussion about the database even necessary in the first place?", your answer shouldn't be "to design a logically perfect structure," but rather:
"It's to solidify the foundation so our product can grow well!"
You should be able to answer like this.
The River of No Return (Because It's Too Hard)
How great would it be if we could swiftly change DB values in just a few seconds, just like switching things with "Replace All" in Google Sheets. And if we thought "Oops, that's wrong," we could just restore it with ctrl + z.
While there's hardly any problem in mobile products that's completely impossible to reverse, there are definitely problems that are hard to undo. We have to discuss them carefully precisely because of that, but we often end up worrying about exactly what to unpack and how. I hope these 3 keywords help out in those moments.
Reference Alarmy's case studies, substitute them into your own service, and throw out these questions. It'll spark a fiery discussion ❤️🔥
👇 Want to debate fiercely over DB design like this?