When Database Relationships Get Complicated | Learning In Public Day 14

Day 14 of building in public, and honestly? I spent most of today fighting with my database instead of building cool features. But here's the thing - sometimes the messy, frustrating days teach you the most.

Quick summary

I'm two weeks into building the Bali Handbook (a travel directory for all the places my partner Grace and I discover here), and today I learned why proper database structure matters. What started as adding simple location coordinates turned into a full day of debugging relationships between tables. Spoiler: it wasn't pretty, but I figured it out.

Why I'm sharing this mess

Look, I could edit out all the mistakes and show you a perfect process. But that's not real life, especially when you're learning to code with AI tools like Claude and Cursor. The actual experience is messy, full of errors, and sometimes you spend 3 hours fixing something that should take 20 minutes.

Living here in Bali, I've learned that the journey matters more than the destination. So here's the real story of what happened when my database relationships got complicated.

The challenge I was facing

So basically what happened was this: I wanted to add distance calculations to the travel directory. You know how when you're staying somewhere, you want to know how far different activities are? That's exactly what I needed to build.

But here's where it gets interesting - I realized I didn't have proper location coordinates stored for individual listings. I had coordinates for general areas (like "Ubud" or "Canggu"), but not for specific hotels or restaurants.

This probably sounds like a simple fix, right? Just add two columns for longitude and latitude. Well, plot twist: it didn't work the first time.

What I tried first (spoiler: it didn't work)

My first instinct was to just add the coordinates to my main listings table. I'm using Supabase for the database, and I thought I could just run a quick SQL command to add the columns.

I opened up the SQL editor, added the longitude and latitude columns, and boom - done. Or so I thought.

Then I went to update my admin dashboard (built with Next.js and Cursor AI) to include input fields for these coordinates. This is where things started falling apart.

The form wasn't showing the new fields. The amenities data disappeared. The room types weren't displaying. I was literally watching my interface break in real time.

The breakthrough moment (after hours of debugging)

You know what I realized? The problem wasn't just adding database columns - it was understanding how all my tables connect to each other.

Here's how my database is actually structured (and why this matters):

• Listings table: Stores basic info for everything (hotels, restaurants, activities)

• Categories table: Defines what type of listing it is (stays, dining, wellness)

• Locations table: General area information (Ubud, Seminyak, etc.)

• Stays table: Specific details for accommodations (room types, amenities)

• Room types table: Individual room information with images and pricing

The issue was that when I added the coordinate fields, my form components weren't properly fetching and displaying the related data anymore. Everything was connected, but the connections were breaking.

Here's how I actually fixed it

Step 1: Understanding the data relationships

First, I had to map out what data lived where. I'm not gonna lie, this was trickier than expected because I'd been building fast without documenting the structure properly.

The key insight was that I needed coordinates at the listing level (for specific locations) AND at the location level (for general areas). This way I could calculate distances accurately.

Step 2: Properly updating the database schema

Instead of just adding columns randomly, I used Claude to help me think through the proper SQL commands:

ALTER TABLE listings 
ADD COLUMN longitude DECIMAL(10, 8),
ADD COLUMN latitude DECIMAL(10, 8);

I also created a new storage bucket for room images, since I was already updating the room types structure.

Step 3: Fixing the form components

This is where things got really messy. My Next.js components weren't pulling the right data after the schema changes. The amenities weren't showing, the timing fields were empty, and the new coordinate fields weren't appearing.

The problem was a mismatch between how data was being stored (as arrays, strings, or null values) and how the form was expecting to receive it.

Step 4: Debugging with console logs

When you're stuck, sometimes you just need to see what's actually happening. I added detailed console logging to track:

• What data was being fetched from the database

• How it was being processed in the components

• What was actually rendering on screen

This showed me that empty strings were being converted to null values, arrays weren't being handled properly, and some fields just weren't being included in the data fetch at all.

The stuff that went wrong

Honestly, almost everything went wrong at some point today:

• The form lost existing data when I added new fields

• Room types weren't connecting to the right table

• Amenities disappeared completely from the interface

• Image uploads for rooms weren't working

• The coordinate fields weren't saving to the database

I was literally talking to my AI assistant (using Super Whisper to convert speech to text - yes, I talk to AI out loud, don't judge me) trying to figure out why basic database operations weren't working.

How I fixed the problems

The solution was actually simpler than I made it: I needed to be more systematic about updating ALL the related files when changing the database structure.

Here's what actually worked:

1. Updated the database schema properly with all necessary columns and relationships

2. Fixed the TypeScript interfaces to match the new data structure

3. Updated EVERY component that touched the affected data

4. Added proper error handling for empty or null values

5. Tested each piece individually instead of trying to fix everything at once

The key was realizing that in a relational database, changing one thing affects everything connected to it. You can't just add a column and expect the rest of your app to magically understand it.

What it looks like now

After all that debugging, here's what's working:

• ✅ Coordinate fields are properly stored and displayed in the admin dashboard

• ✅ Room types are connected to their own table with image upload capability

• ✅ Amenities and timing information display correctly

• ✅ The form saves data without throwing errors

• ✅ I can add longitude and latitude for accurate distance calculations

The front-end display still needs work (that's tomorrow's task), but the foundation is solid now.

Lessons I'm taking from this

• Database relationships are everything - change one thing, update everything connected

• AI tools are amazing but you still need to understand the basics - I can't just copy-paste solutions without understanding why they work

• Console logging saves your sanity - when things break, you need to see what's actually happening

• Building fast is great, but documenting structure matters - I should have mapped out my database relationships from the start

What surprised me most? How much time I spent on what seemed like a simple feature. But that's the reality of development - sometimes adding two database columns turns into a full day of debugging.

What I'm working on next

Tomorrow I'm focusing on the user-facing side:

• Getting room types to display properly on the listing pages

• Adding the map functionality with the new coordinates

• Improving the styling and layout of the stays pages

• Maybe adding that image compression feature I keep talking about

I'm also planning to create a proper database diagram so I don't run into these relationship issues again.

Final thoughts

You know what's funny? I used to think coding with AI would make everything easier and faster. And it does, in many ways. But it also makes it easier to build complex things without fully understanding them.

Days like today remind me why I'm doing this 60-day challenge. It's not just about building a travel directory - it's about actually learning how to build things properly. The messy, frustrating days are just as valuable as the smooth ones.

If you're following along with this series or building something similar, don't get discouraged when things break. That's how you learn. Plus, the feeling when you finally fix something that's been driving you crazy? Totally worth it.

Following the 60-day challenge? I'm sharing everything - the wins, the failures, and all the messy bits in between. Tomorrow we tackle the front-end display issues and hopefully make some actual visual progress.