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.
Top comments (1)
Forgive the jokes.