Database Schema Design Mistake That Broke My App
How real-world usage revealed a fundamental flaw in my database design
TL;DR: My Johnny Decimal index app had a three-level database schema when the system requires four levels. I’d conflated folder containers with the files inside them—a fundamental data model flaw. One week of dogfooding caught what months of planning completely missed. Key lessons: use your own software early, match your data model to your mental model, and refactor before you accumulate data you’ll need to migrate.
I’d been using JDex, my Johnny Decimal index application, for about a week when a fundamental database schema design flaw became impossible to ignore.
The app worked beautifully. I could create areas and categories. I could track items with their XX.XX identifiers. The search was fast, the UI was clean, and I was feeling pretty good about what I’d built.
Then I tried to organize real files.
Somehow I had folders and the files inside them sitting at the same level in my database. My data model had three levels when Johnny Decimal requires four.
Here’s how one week of dogfooding caught what months of planning completely missed and the four lessons I’m carrying into every future project.
The Problem I Didn’t See Coming
How Johnny Decimal Hierarchy Actually Works
Johnny Decimal (johnnydecimal.com) is a system for organizing files and information. It has a clear four-level hierarchy:
Area (00-09)
└── Category (00, 01, 02...)
└── ID Folder (00.01, 00.02...)
└── Actual Files
Level What It Is Number Format Example
1 Area 00 - 09 00-09 System
2 Category XX 00 Index
3 Folder XX.XX 00.01 JDex Database
4 Item XX.XX.XX 00.01.01 Main DB File
My database had three levels: areas, categories, and... items.
Where do the ID folders go?
Why My Three-Level Database Schema Failed
I’d conflated two fundamentally different things:
Folder containers: the XX.XX numbered folders that hold files
Actual files: the contents inside those folders
In my original schema design, “00.01 JDex Database” (a folder) and the SQLite file inside it (an item) existed at the same level in the database. That’s like saying a filing cabinet drawer and the documents inside it are the same thing.
Why Database Schema Mismatches Break Core Functionality
This wasn’t just aesthetically wrong. The three-level data model broke features I needed:
I couldn’t track what was inside a folder. The whole point of Johnny Decimal is knowing “everything about taxes lives in 12.04.” But my schema had no way to list what was actually in 12.04. The folder and its contents were peers, not parent and child.
Folder-level properties were impossible. What if everything in a folder should be marked as sensitive? With my original design, I’d have to set that flag on every individual item. There was no way to say, “This container and everything in it shares this property.”
The numbering didn’t work. Johnny Decimal items should be XX.XX.XX—three segments. Mine only went to XX.XX because I’d eliminated an entire level.
When your data model doesn’t match the mental model of the system you’re building, you’ll keep running into walls. The database fights you instead of helping you.
The Fix: Adding a Fourth Level
The solution was straightforward conceptually but required rethinking the entire database schema: the old items table became folders. A new items table was created for the actual tracked objects inside those folders.
The Refactored Database Schema
Here’s what the corrected structure looks like:
-- Folders (Level 3) - XX.XX format containers
CREATE TABLE folders (
id INTEGER PRIMARY KEY,
folder_number TEXT UNIQUE, -- “22.05”
category_id INTEGER,
name TEXT,
sensitivity TEXT DEFAULT ‘standard’,
created_at TIMESTAMP,
updated_at TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(id)
);
-- Items (Level 4) - XX.XX.XX format objects
CREATE TABLE items (
id INTEGER PRIMARY KEY,
item_number TEXT UNIQUE, -- “22.05.03”
folder_id INTEGER,
name TEXT,
sensitivity TEXT DEFAULT ‘inherit’, -- Can inherit from folder
file_path TEXT,
notes TEXT,
created_at TIMESTAMP,
updated_at TIMESTAMP,
FOREIGN KEY (folder_id) REFERENCES folders(id)
);
The inherit option for item sensitivity was a bonus insight that emerged from the refactor. Most items should just use their folder’s sensitivity level. Only exceptions need explicit overrides. That’s not something I could have implemented with the old three-level schema.
Four Lessons from This Database Design Mistake
Use Your Own Software (Dogfooding)
The schema flaw was invisible in design documents and code reviews. It only appeared when I tried to organize real files with real data.
A week of daily use caught what months of planning missed.
There’s no substitute for using your own software to accomplish real tasks. You’ll discover conceptual mismatches that no amount of testing or review will reveal. If you’re building something, use it yourself as soon as it’s minimally functional.
Match Data Models to Mental Models
I knew Johnny Decimal had four levels. I could draw them on a whiteboard. Yet somehow my database had three.
That mismatch was always going to cause problems—I just hadn’t encountered them yet. When you’re modeling a real-world system, count the levels. Write them down. Then count the levels in your database. Do they match?
Containers and contents are different things, even if they both have names and locations. This seems like common sense now, but when I was building the original schema, I simply didn’t see the issue.
Database Schema Changes Are Easier Early
This refactor touched every database function and most UI components. It was significant work.
If I’d caught this flaw after six months of data entry, migration would have been painful. I’d have needed to write migration scripts, test them against production data, handle edge cases, and pray nothing broke.
Catching it in week one meant a clean restart. I dumped all the data because it was easier than trying to preserve and migrate it. That’s a luxury you only have early in a project.
Keep Your AI Context Updated
I’ve been bouncing ideas off Claude to see what might work and what definitely won’t. It’s been helpful for thinking through problems and catching issues.
But here’s what I didn’t anticipate: my Claude Project configuration had the old three-level schema baked in. After the refactor, I had to update those instructions so the AI understood the new structure and why I changed it.
The configuration is only as good as the context it contains. If your tools are working from outdated assumptions, they’ll give you outdated guidance. This applies to AI assistants, documentation, onboarding materials, and anything that encodes how your system works.
I should have just let Claude help build the schema in the first place. But I was trying to learn as I went, and that’s the tradeoff. I’m not a coder by trade, just a systems engineer who likes to automate as much as possible.
📬 Learning to code while working full-time? You’re not alone. Subscribe to As The Geek Learns for weekly lessons from the trenches—the wins, the fails, and the “I wish someone had told me” moments.
Database Design Checklist for Hierarchical Data Projects
If you’re building something that models a hierarchy, run through this before you write code:
Count the levels in the real-world system. Write them down. Then count the levels in your database schema. Do they match exactly?
Distinguish containers from contents. Are there things that hold other things? Those need separate tables or entities, not just a “type” field.
Define the numbering scheme. If your system has identifiers like XX.XX.XX, your schema needs to support generating and validating that format at each level.
Identify inheritable properties. What attributes should cascade from parent to child? Design for that explicitly (like the sensitivity: inherit option I added).
Plan for schema evolution. Your first model probably won’t be your last. How hard will it be to add a level later? To split a table? Design with migration in mind.
What’s Next for JDex
JDex is better now. The four-level hierarchical database design matches how Johnny Decimal actually works, and I can finally track what’s inside a folder.
The most important bugs aren’t always in your code; they’re in your assumptions. The schema looked right. The code worked. But the underlying model was wrong, and no amount of polish would have fixed that.
One week of real use revealed what months of planning couldn’t.
James is a systems engineer building JDex as a learning project while documenting the journey. Follow along at As The Geek Learns for more lessons from the trenches of learning to code.
Have you discovered a fundamental flaw hiding in your project’s design? How did you find it? Drop your story in the comments. I read every one.
If you’re learning to build software while working a day job, subscribe to As The Geek Learns. I document the wins, the fails, and the “I wish someone had told me this earlier” moments.