TLDR: If a metric varies by both sides of a many-to-many relationship, it cannot live on either entity. It has to live on the join. I learned this building an ecommerce business's supplier fallback chain — twice, actually.

The Standing Business Rock

my mentor asks the same question at almost every L10 (the weekly leadership meeting at an ecommerce business, an ecommerce business, the supplement brand).

"Where are we on the second supplier?"

Six meetings running. Dual-sourcing our hero ingredient. The product that keeps the lights on.

He's completely right to push. Single-sourcing a hero SKU is a serious supply chain risk, and building a fallback chain into the app was one of the first things I prioritized.

I got it wrong. Twice.

Break #1: The Wrong Entity

My first pass added a "primary supplier" picker per product — a simple dropdown tied to contacts filtered on roles CONTAINS 'supplier'.

The problem? That filter excluded every co-packer in the database.

an ecommerce business places POs with the co-packer (the manufacturer who makes the finished product). Not the raw-material supplier like a raw-material supplier. Two completely different entities in the supply chain, and I'd wired the picker to the wrong one the whole time.

Fix was a full rename: supplier → vendor everywhere, filter updated to include both roles. Now the dropdown finally showed the primary co-packer, my contact at another co-packer, a third co-packer. All the people my client actually calls when she needs to reorder.

Okay, great. Fixed. Except…

Break #2: One Number Can't Hold It

With the right entities in place, I stored lead time as a single field on the vendor record — vendor_details.lead_time_days. One number. Clean.

Then my client said this on our call:

"One co-packer might be 20 weeks for SKU-A… versus another might be 12 for something else."

One co-packer's lead time for SKU-A is 20 weeks. Their lead time for SKU-B is 8 weeks. Same vendor. Wildly different numbers depending on which product you're ordering.

My single field collapsed. And if your lead time is wrong, your reorder forecast fires at the wrong time, and you're staring down a stockout before the PO is even placed.

The Fix: Move It to the Join

The right model is a vendor_products join table:

vendor_products (
  vendor_contact_id,
  product_id,
  rank,           -- 1 = primary, 2 = secondary, 3 = tertiary
  lead_time_days,
  is_active,
  ...
)

Lead time lives on the assignment — the specific pairing of a vendor with a product. So does the rank. So does the active toggle (mark a vendor inactive for a given SKU and the app falls back to the next in line automatically).

The primary/secondary/tertiary chain isn't a property of the vendor.

It's a property of the relationship.

Why This Keeps Happening

The one-number-per-party model always looks right at first. Early designs usually have one vendor per SKU, so a single field works fine. You ship it, it's clean, nobody complains.

The moment a second vendor enters the picture — or the moment reality doesn't match your assumption — the cracks show up ALL AT ONCE.

The rule I wrote down:

If a metric varies by both sides of a many-to-many, it cannot live on either party. It has to live on the join row.

I've had to relearn this three times in this one codebase. Writing it here so maybe it sticks.

This one matters because my mentor's been asking about backup sourcing for over a year. The app can now actually model it. Not just who the fallback vendor is — but what their lead time is for that specific SKU, and whether they're currently active. That's the kind of thing that turns a spreadsheet question into a real answer.

P.S. The Postgres rename-column gotcha is a separate post worth writing — renaming FK columns doesn't propagate into function bodies. report_forecast silently broke. Found it. Fixed it. Topic for another day.