When Bad Schema Design Costs You a Rewrite PT 2. of 3

The thought of this is quite painful but then what can I do about it?

Walk with me as we go on the journey on how I wanted to implement my "million dollar idea" that will have me cost me "a million dollars".

In the last episode, (in case you missed it here), I walked you through my beautiful notification service design. Today, we’re taking a painful stroll through my supposed multi-tenant role-based access control system

The Design

This was my brilliant RBAC design for a multi-tenant application. At the time, it made perfect sense.

Looking at it now… I can’t help but see the expensive mistake staring back at me.

This is it.

If you have a keen eye, you would have noticed the wide range of issues from this db designs. But if you are a newbie and can't figure it out don't worry, let me show you were I would have spent the million dollars...

The Brutal Reality

Let me start with the most painful part; the part that hurt my pride the most.

There is no RBAC here.

Not hidden somewhere. Not half-implemented. Not “coming in version two.” It simply does not exist.

There are no roles.

There are no permissions.

There is no mapping between roles and permissions.

There is no assignment of roles to users.

There is no access control system.

What I built was a companies table, a users table, and a foreign key connecting the two. And somehow, in my head, that qualified as “multi-tenant role-based access control.”

It wasn’t.

It was multi-tenant authentication.

Every user inside a company is just… a user. That’s it. No hierarchy. No structure. No separation of responsibility. No difference between someone who should manage payroll and someone who should only view their own dashboard.

Where is the Admin?

Where is the Manager?

Where is Finance?

Where is the Viewer?

Nowhere.

The “role-based” part of role-based access control never even made it into the database. It existed only in my imagination.

And that’s the kind of realization that makes you close your laptop slowly and stare at the ceiling for a while.

The "correction": Proper RBAC design.

After careful consideration on the blunder I called a DB design, this was the final update on this multi-tenant RBAC design.

This version is what emerged after I stopped pretending that a foreign key was RBAC and started modeling the relationships properly.

(I duplicated the table company_users_roles table twice an error on my part)

The first big shift was this: I separated identity from tenancy.

A user is no longer tied directly to a company. A user simply exists. Clean. Global. Independent. The company relationship now lives in company_users, which represents membership not identity.

That alone changes the mental model of the system.

But I didn’t stop there.

At first, I added role_id directly to company_users. It felt logical. A user joins a company, they get a role. Simple.

Too simple.

Because reality is rarely that neat.

People wear multiple hats. Someone can be part of Finance and still act as a Manager. Someone can be an Admin but also belong to a restricted operational role. The moment you force “one role per user per company,” you’re no longer modeling reality; you’re modeling convenience.

So I introduced another table: company_user_roles.

Now the structure reflects what’s actually happening.

A user belongs to a company.

And within that company, that user can hold multiple roles.

That separation matters.

Membership is one concern. Authority is another.

They deserve different tables.

And once that distinction is made, the rest of the RBAC model starts to make sense. Roles map to permissions. Permissions define actions. Users inherit permissions through their assigned roles inside a company context.

Now, when I look at the schema, it doesn’t feel like imagination anymore. It feels intentional.

But here’s the interesting part.

Even with this corrected schema, I realized something uncomfortable: designing RBAC isn’t just about adding tables until it “looks right.” It’s about deciding who owns control. Are roles global? Are they tenant-scoped? Can tenants customize them? Do permissions merge across roles? What happens when roles conflict?

Those questions don’t show up in a schema diagram.

They show up in production.

And that’s the difference between building something that works… and building something that scales.

The schema below is cleaner. More honest. More flexible.

But the real growth wasn’t in the tables.

It was in finally understanding what problem I was actually solving.