### Architecting an Enterprise Retail POS Database A large-scale retail chain (similar to Walmart or Carrefour) operates across 25 regional branches. Each branch runs 8 to 20 active checkout counters simultaneously, managing an inventory catalog exceeding 50,000 SKUs. Processing over 100k+ daily barcode scans requires an ultra-low-latency database architecture capable of live stock deductions, VAT calculation, discount engine evaluation, and strict multi-user concurrency. ---\n ### 📊 Enterprise ERD Core Structure To prevent anomalies, master entities are strictly decoupled from transactional ledgers: * **Master Entities:** `Branch` ➔ `Counter` ➔ `Product` ➔ `ProductStock` (Current balance snapshot). * **Transactional Ledgers:** `SalesInvoice` ➔ `SalesItem` ➔ `Payment` ➔ `StockLedger` (Append-only movement audit). ```mermaid graph TD B[Branch] --> C[Counter] C --> SI[SalesInvoice] SI --> SIT[SalesItem] P[Product] --> SIT P --> PS[ProductStock] PS --> SL[StockLedger Audit] ``` ---\n ### ⚡ Real Production Indexing Strategy POS checkout queues cannot tolerate full table scans. Enterprise engineers apply targeted B-Tree indexes across lookup vectors: 1. **Product Lookup:** `CREATE INDEX ix_product_barcode ON pos_products(barcode);` 2. **Date Filtering:** `CREATE INDEX ix_sales_branch_date ON pos_sales(branch_id, sale_date);` 3. **Stock Concurrency:** `CREATE UNIQUE INDEX ux_stock ON pos_products(branch_id, product_id);` ---\n ### 🔒 Resolving Multi-Counter Concurrency (The Oversell Problem) Suppose Product A has exactly 2 units remaining in stock. Counter 1 attempts to sell 2 units while Counter 2 attempts to sell 1 unit simultaneously. Without isolation, both reads pass, leading to negative inventory overselling. #### The Enterprise Solution: Pessimistic Concurrency Locking To prevent race conditions, the checkout stored procedure executes an explicit row lock during validation: ```sql BEGIN TRANSACTION; -- Lock the specific stock row against concurrent modifications SELECT stock_quantity FROM pos_products WHERE id = 101 FOR UPDATE; -- Verify quantity >= requested amount, then update UPDATE pos_products SET stock_quantity = stock_quantity - 2 WHERE id = 101; COMMIT; ``` ---\n ### 🧱 Database Partitioning & Offline Sync * **Monthly Partitioning:** Tables like `SalesInvoice` grow into tens of millions of rows annually. Partitioning by month (`sales_2026_01`, `sales_2026_02`) ensures index trees remain small and high-speed. * **Offline Branch Resiliency:** If WAN internet connections fail during peak retail hours, local checkout terminals store transactions locally in an embedded SQLite queue, executing an asynchronous batch synchronization to the central master DB once connection restores.