July 2017, our first purchase-sale-inventory system. The client was a grocery wholesaler with a warehouse on the city's edge; the owner's office held stacks of handwritten receipts, red and white, carbon-copied in duplicate. His request was refreshingly concrete: don't make me re-add everything on a calculator at month-end.
How the 3 Tables Were Decided
The first database really did have just 3 tables.
goods holds products: id, name, spec, unit, purchase price, sale price — 6 fields.
orders holds documents: id, type (1 purchase, 2 sale), counterparty (text), date, handler, remark — 6 fields.
order_items holds line items: id, order_id, goods_id, quantity, unit price — 5 fields.
No separate suppliers table — the counterparty was typed straight into a text field on purchase orders. No bin locations, no batches, and cost accounting used the simplest moving weighted average, recomputed on every purchase. In hindsight all of that should exist, but what the owner feared most was 'the system being more trouble than paper.' Every extra table is one more thing to maintain.
The Entry Screen Was Harder Than the Database
Where the time actually went was the order-entry page. The warehouse had one ancient computer, its F1–F12 labels worn off, operated by a lady who couldn't get used to a mouse. We made entry fully keyboard-driven: Enter moves to the next field, typing the pinyin initials of a product name opens a dropdown, and after quantity, Enter saves and opens a new order. jQuery keydown bindings — that alone took two days of tuning.
One afternoon I stood in the warehouse watching her enter 40-odd orders and noticed she kept looking up at the price list on the wall, then down to copy the price. The next day we added auto-filled sale prices in the dropdown. She never looked at that wall again.
After V1 Went Live
It ran for a month, 2,000+ rows of data. Every evening the owner printed a daily summary on one A4 page: sale total, purchase total, outstanding credit. Month-end reconciliation still happened, but no more digging through paper slips.
In month five we added the 4th table: suppliers. The same supplier had been written three ways — 'Zhang's,' 'Zhang's Grocery,' 'Zhang's Wholesale' — and the books wouldn't reconcile. On the day we added it, we ran a de-duplication over the counterparty field of every historical order and manually verified 60+ entries before merging.
A Footnote
Over time the system gained stores, stocktakes, Excel export; the tables grew past a dozen. But with every change we went back to look at those 3 tables: are documents and line items still cleanly separated? As long as they are, whatever grows on top stays untangled.
The stack of handwritten slips was never thrown out. The owner said he's keeping them — in case the system ever won't open.
