Fixing Database Relations for Cuisine Types & Dietary Options | Learning In Public Day 19

Ever spent hours wrestling with database relationships only to discover you've been asking the wrong question the entire time? Yeah, that was my morning.

Quick summary

I spent way too long trying to fix cuisine types and dietary options in my Bali travel directory. What should have been a simple database restructure turned into a debugging nightmare that taught me more about junction tables than I ever wanted to know. But hey, it works now.

Why I'm sharing this mess

Look, I could pretend this went smoothly and write some polished tutorial. But that's not how building actually works, especially when you're learning in public. This was one of those days where everything that could go wrong did go wrong, and I think there's value in showing the real process.

Plus, if you're building anything with complex data relationships, you'll probably hit similar walls. Maybe my mistakes can save you some time.

The problem I was trying to solve

So I'm building this travel directory for Bali, and I had cuisine types and dietary options stored as simple JSON arrays in my dining table. It worked, but it wasn't scalable. Every time I wanted to filter restaurants by cuisine type or add new dietary options, I had to mess with these arrays.

What I really needed was proper relational tables. You know, the kind where you can actually query things efficiently and don't have to parse JSON every time someone wants to find vegan Japanese restaurants in Ubud.

My first attempt (spoiler: it didn't work)

I started by asking my AI assistant to help me denormalize the JSON data and create separate tables for cuisine types and dietary options. The logic was sound:

Create a cuisine_types table
Create a dietary_options table
Create junction tables to link them to restaurants
Remove the old JSON columns once everything worked

Simple, right? Wrong.

Where everything went sideways

Here's where I made my first mistake: I didn't clearly specify how the junction tables should work. The AI created the tables, but when I tried to save data, it kept trying to store things in the old JSON columns instead of the new relational structure.

I spent like an hour debugging this. The form would show the cuisine types, I could select them, but nothing would save to the new tables. Meanwhile, the old JSON columns were still getting populated.

You know what's frustrating? When you can see the data in the UI, but it's coming from the wrong place entirely.

The debugging nightmare begins

This is where things got really messy. I had:

Old JSON columns still being written to
New relational tables sitting empty
Junction tables that existed but weren't being used
A frontend that was pulling data from... somewhere

I started adding debug statements everywhere. Console logs, database queries, you name it. My terminal looked like a Christmas tree of debugging output.

The worst part? I forgot to record my screen for like 20 minutes while I was deep in the debugging zone. So if you're watching the video, there's this awkward gap where I'm just explaining what happened off-camera.

The breakthrough (finally)

After way too much trial and error, I realized the core issue: the relationship mapping was using id instead of dining_id for the foreign key relationships.

Such a simple thing, but it meant the database couldn't find the connections between restaurants and their cuisine types. The junction tables were there, the data structure was right, but the linking logic was broken.

How I actually fixed it

Once I figured out the foreign key issue, here's what I had to do:

Step 1: Fix the relationship mapping

I had to explicitly tell the system to use dining_id as the foreign key in the junction tables, not just id. This seems obvious in retrospect, but when you're deep in debugging mode, you miss these things.

Step 2: Update the form logic

The frontend form was still trying to save to the old JSON columns. I had to rewrite the save logic to:

Insert cuisine type relationships into the junction table
Insert dietary option relationships into their junction table
Stop trying to update the old JSON columns

Step 3: Fix the display logic

The restaurant detail pages weren't showing the new relational data because they were still looking for the old JSON arrays. I had to update the queries to pull from the junction tables and display the related cuisine types and dietary options.

Step 4: Clean up the mess

This meant removing all those debug statements I'd scattered everywhere and eventually dropping the old JSON columns from the database.

What the final solution looks like

Now I have a proper relational structure:

cuisine_types table with id, name, and icon
dietary_options table with id, name, and icon
dining_cuisine_types junction table linking restaurants to cuisines
dining_dietary_options junction table linking restaurants to dietary options

The admin form shows checkboxes for each option, making it easy to select multiple cuisine types or dietary restrictions. The frontend displays them properly with icons, and I can actually query and filter efficiently now.

The stuff that still went wrong

Even after fixing the main issue, I had a few more problems:

The price range wasn't saving (turned out to be a separate form field issue)
Some cuisine types were showing with their icon names appended (like "Japanese bread-slice")
The dining detail page wasn't displaying the new relational data

Each of these took additional debugging rounds. It's like fixing one thing reveals two more broken things.

Lessons I'm taking from this

Be specific about foreign keys from the start. When working with AI assistants on database design, I need to be crystal clear about how tables should relate to each other. "Create a junction table" isn't specific enough.

Test the full flow immediately. I should have tested the complete save-and-display cycle right after creating the tables, not after building the entire form system.

Junction tables are powerful but finicky. They're the right solution for many-to-many relationships, but getting the foreign key mappings right is crucial.

Sometimes you need to step back and restart. I probably could have saved time by scrapping the broken attempt and starting fresh with a clearer plan.

Why this matters for the bigger picture

This restaurant directory is just one part of the travel platform I'm building. But getting the data structure right now means I can reuse this pattern for beach clubs, hotels, and other venue types later.

The cuisine types and dietary options tables can be shared across different venue types, which is exactly what I was hoping for. A vegan-friendly beach club can use the same dietary option tags as a vegan restaurant.

What I'm working on next

Now that the database structure is solid, I need to:

Add proper filtering to the restaurant listing page
Implement the same pattern for beach club amenities
Start adding real restaurant data (Grace and I are doing a research trip this weekend)
Add menu upload functionality for restaurants

The technical foundation is there now. Time to fill it with actual content.

Final thoughts

This was one of those development days where you feel like you're going in circles, but you're actually making progress. It's messy and sometimes frustrating, but that's how you learn.

If you're building similar database relationships, my advice is to start simple and be very explicit about your foreign key mappings. And maybe don't try to fix everything at once like I did.

Living here in Bali and building this in public has taught me to appreciate these learning moments, even when they're frustrating. Every debugging session makes the next one a little easier.

Following along with my 60-day challenge? I'm documenting everything as I build this travel directory from scratch. Some days are wins, some days are like this one. But that's the real process of building something from nothing.