I didn’t really think database design would be the part that slows me down. I expected JavaScript, maybe APIs, maybe deployment. Not tables.

But the moment I started trying to design an inventory system properly, I ran into something called Boyce-Codd Normal Form. BCNF sounded simple when I first read about it(No I'm kidding). Then I actually tried applying it, and everything started feeling even less clear.

At some point, I had a working schema. Products, sales, purchases, stock movements. It all worked. Kind of!
My first mistake was thinking normalization was about splitting tables until everything looks clean.

So I kept breaking things apart:

• products table
• categories table
• sales table
• sale items
• purchases
• stock

But I still had duplicated logic everywhere, especially around stock.

At some point I had both:

• a stock table
• a stock_movements table And I was updating both. Honestly I thought it was tradeoff or something(I know I'm stupid) but coincidentally I was also reading about database design(so not that stupid).

And then came BCNF

BCNF sounded like this abstract rule:

every determinant must be a candidate key

That sentence didn’t help me much at first.

What I understood instead was simpler:
if something depends on something that is not a key, you probably did something wrong.

Even that was still hard to apply.

And it only clicked when I started writing the events and triggers.
So the two sources of truth in my database didn't look that good.

I removed the stock table and replaced it with a view based on stock_movements.I used a view because stock was a derived quantity.So I eliminated duplication and stored one fact only.
**
Problems with other derived quantity**
So I initially thought that sales totals should be stored in the table,purchases table should store information about quantity and price and should I use triggers for certain things such as update the stock movements table after a sale, purchase or spoilage ,or should i implement it in the application logic.

I ended up coming back to the

one source of truth

so I did use triggers to update the stock_movements table.I also did not use derived quantities instead for the sales I decided I would have a header table and line items table -a sales and sale_item. The Sale table held the sale information(saleId,date,payment method,customer name, total), while the sale item held information about the products.Same applied to purchases.

What I learned

BCNF was the relationships we made along the way.