What Nobody Tells You About Designing a Database from Scratch

When I decided to build my own system for the 3PL company where I work, I had no software development experience. I had something different: I knew the operation from the inside.

I knew exactly which data mattered, which was noise, and what flow the information had to follow for the system to be truly useful. What I didn't know was how to translate that into tables, relationships, and efficient queries. And that turned out to be the most important work of the entire project.

#First, understand which data really matters

Before opening Supabase, I did something any industrial engineer would do instinctively: I went to the source of the problem. Clients sent us pre-alerts in Excel. Each had their own format — different columns, different names, different order. But we worked with a data standard. So the first step was identifying what that standard was.

Having that standard clear before designing the first table was the most important decision of the entire project. I didn't start with technology — I started with data. And I knew the data because I lived the operation every day.

#The design that wasn't a redesign, but an evolution

I redesigned the tables three or four times before they felt right. But they weren't traumatic redesigns — they were evolutions. As I progressed module by module, I better understood the relationships I needed and added columns that made the information more coherent.

I learned this in the data analytics master's: thinking about relational databases isn't just defining tables — it's designing the flow of information. How one entity connects to another, how to avoid duplicating data, how to ensure that if something changes in one place, the change propagates correctly to the rest.

The difference between a well-designed and a poorly designed database isn't always visible on day one. It shows when the system grows, when cases you didn't anticipate appear, when someone does something unexpected and the system has to respond coherently.

That's why I went slowly. Module by module. Understanding well what I was doing before moving to the next. I never had to redo anything from scratch — I had improvements, not re-engineering.

#The decision a conventional developer wouldn't have made

There's a feature in the system that was born directly from knowing the operation: cargo deconsolidation.

In logistics, sometimes a package that arrives as one unit needs to be split into several packages to be sent to different destinations. The standard approach I'd seen in other ERPs was simple: mark the package as deconsolidated and create the new packages manually. It technically works. Operationally, you lose traceability.

In logistics, knowing where each package came from isn't a detail — it's a requirement.

So I designed the tool differently: when you deconsolidate a ticket, the system marks it as "repacked" and creates child tickets linked to the parent. The parent-child relationship is recorded in the database. You can see at any time where each package came from, what was done with it, and where it went.

A developer who doesn't know logistics would have implemented the simple version — the one they saw in the reference ERP — because it technically solves the problem. I implemented the version with full traceability because I knew exactly why that traceability mattered.

#Silent errors — and how I learned to find them

It was in production where I learned that silent errors existed. The system worked. No crashes, no corrupted data, nothing breaking the flow. But something didn't look right.

A pre-alert marked as incompletely received appeared in two modules at the same time: Pre-alerts and Warehouse. It was correct for it to be in both — an incomplete reception needs follow-up on both sides. The problem was there was no visual indication that it was incomplete. No badge, no color, no signal — it just appeared like any other pre-alert. And that created confusion.

It worked. But it wasn't understood.

That's the silent error: it doesn't break anything, doesn't generate an alert, doesn't appear in any log. But it creates ambiguity. And ambiguity in an operational system is dangerous, because people make decisions based on what they see — and if what they see isn't clear, the decisions aren't either.

The fix was simple: a badge indicating "Incomplete reception" in both modules. But finding the problem required something no tutorial covers: using the system with critical eyes, as someone who doesn't know what they're looking at.

Since then, that's my test for every new module: would someone without context understand it? Or does it require me to be there to explain it? If it requires explanation, the design isn't finished.

#What I learned