Inventory management at a small team is usually one Excel file. And that file will eventually run into the same thing: the numbers on paper stop matching the stock on the shelf, and you can't find which entry was wrong.
The cause is almost always the same — someone edited the "quantity on hand" cell directly.
The 30-Second Overview
| Item | Approach |
|---|---|
| Core principle | Stock is calculated, not typed in |
| How many tables | Three: items, movements, suppliers |
| Where current stock comes from | A rollup that sums the movement quantities |
| Most valuable feature | Automatic alerts below the safety stock level |
| When to replace it | Multiple warehouses, lot numbers and expiry dates, barcode scanning |
Core Principle: Stock Numbers Should Never Be Edited by Hand
The spreadsheet approach keeps a "quantity on hand" column: subtract on shipment, add on receipt. The problem is that this column has no history, a wrong edit is invisible, and two people editing at once overwrite each other.
The right approach is that stock isn't a field, it's a computed result. What you record is every movement in and out; the system calculates the quantity on hand.
That way, whenever a number looks questionable, you can trace back to the entry that caused it.
Three Tables Are Enough
| Table | What goes in it |
|---|---|
| Items | Name, part number, unit, safety stock level, supplier |
| Movements | Date, item (link), in or out, quantity, reason, handler |
| Suppliers | Name, contact details, lead time |
The key is that movements link to items, and then a rollup on the items table sums every movement for that item — that's your quantity on hand, and it's always right.
For "in or out," store signed numbers so the sum is trivial. Shipments go in as negatives, receipts as positives.
A Few Design Choices That Get It Actually Used
However well the system is designed, it's pointless if the people on the floor don't use it. These things matter a lot:
Log movements through a form view. Don't make warehouse staff edit the table directly — give them a form with just four fields to fill in. It works on a phone, so they can record it standing in front of the rack.
Watch restocking in a kanban view. Columns by status make it obvious at a glance what needs reordering. That's far more useful than staring at a 200-row table.
Set the safety stock field. Then hook up automation via webhook so a notification fires when stock drops below the threshold — this is the single most valuable feature in the whole system, because it's the only part that comes to you.
When to Replace It
Let's be honest. NocoDB is a general-purpose database tool, not a warehouse system. It can't carry the following requirements, and forcing it will hurt:
- Transfers between warehouses. Quantities of the same item in different warehouses and movements between them make the data model start to get complicated.
- Lot number and expiry management. Food, pharmaceuticals, and cosmetics need FIFO and expiry tracking — that's specialist territory.
- Barcode scanning for receipt and issue. There's no ready-made scanning flow; you'd be wiring up the hardware yourself.
- Real-time sync with payments and e-commerce platforms. You can connect it with automation, but if your requirements for immediacy and consistency are strict, a specialist system is more reliable.
The test is the same as for any home-built system: once maintaining the tables themselves starts eating serious time, the license fee you saved is already gone.
Start From the Sorest Point
Don't design the complete data structure up front. Solve one concrete pain first — usually "we don't know when to reorder."
Build the items table plus the safety stock level plus low-stock alerts, and use it for two weeks. When the people on the floor start complaining about something else, those complaints are what you should design next.
Systems where the full architecture was drawn before rollout usually end up elegant and unused.
Start From the Sorest Point
Don't design all three tables up front. Solve one concrete pain first — usually "we don't know when to reorder."
Version one does exactly three things: the items table, a safety stock field, and low-stock alerts. Use it for two weeks.
Then wait for the people on the floor to start complaining about something else — "we don't know when this batch came in," "how much did we write off last month?" — because those complaints are what you should design next.
Systems where the full architecture was drawn before rollout usually end up elegant and unused.
How to Handle Stocktakes
When physical stock doesn't match the system's number, don't just edit the number — that strips the history of its meaning.
The right move is to add a "stocktake variance" movement record, with the difference as the quantity and "stocktake adjustment" as the reason. The system's number then matches, and later you can see how many times you adjusted this month and by how much.
The number of adjustments is itself a signal: if you need large adjustments every month, somewhere in the process the records aren't being filled in properly.
Why "Quantity on Hand" Can't Be a Field
The spreadsheet approach keeps a "quantity on hand" column: subtract on shipment, add on receipt. That design has three problems:
- No history — when a number is wrong, you don't know which edit broke it
- It gets overwritten — two people editing at once, last save wins
- Nowhere to start when it doesn't match — all you can do is count everything again
Switch to "record every movement and let the system sum it up" and all three problems disappear at once. Whenever a number looks questionable, you can trace back to the entry that caused it.
That's the only decision in this whole design that truly matters. Everything else is detail.
Which Fields the Movements Table Needs
| Field | Why you need it |
|---|---|
| Date | Reconciliation and traceability |
| Item (link) | The basis for the rollup |
| Quantity (signed) | Receipts positive, shipments negative, so summing is trivial |
| Reason | Sale / return / write-off / stocktake variance |
| Handler | The most useful column when the numbers don't match |
| Notes | Explanations for anything unusual |
Plenty of people skip the "reason" column, but it determines whether you can later answer questions like "how much did we write off this month?" Adding a dropdown costs almost nothing.
FAQ
Q: Do I really need three separate tables?
Items and movements are required; suppliers is optional. If you only have two or three suppliers and their details never change, just put them in a field on the items table.
Q: What if the people on the floor won't use it?
Use a form view. Give them a form with four fields that works on a phone — no need to learn the whole system, and they can't see the rest of the data either.
Q: How do I set up low-stock alerts?
Add a safety stock field to the items table and hook a webhook up to an automation flow that sends a message when stock falls below the threshold. It's the only part of the system that comes to you, and the most valuable feature in it.
Q: Can I scan barcodes?
There's no ready-made scanning flow; you'd wire up the hardware yourself or pair a phone scanning app with a form. If barcodes are central to your process, you should be looking at a dedicated warehouse system.
Q: Does it get slow as data grows?
The movements table keeps accumulating, and rollups become noticeable once the number of linked records is large. Consider archiving old data periodically, or switching to a scheduled calculation stored in a regular field.
Sources and Further Reading
For NocoDB's field types and permission model, the official documentation is authoritative:
Further Reading
- Introducing NocoDB: The Open-Source Airtable Alternative
- NocoDB as a CRM: When It's Enough and When to Move On
- NocoDB Guide: Building Your First Table and View
- n8n Plus NocoDB: Keeping Your Automation Data in Your Own Hands
- NocoDB API Quickstart: Getting a Token, Reading and Writing Tables, Connecting Automations
- RoamerHost NocoDB Hosting Plans
Want Someone to Build It for You?
Once the data volume climbs, or you need to connect this to your company's existing systems, it stops being just a matter of picking a tool. Roamer Tech takes on custom enterprise system development and API integration:
