POS System Database Schema Design: A Complete Guide
A Point of Sale (POS) system database needs to handle high-volume transactions with low latency while maintaining strict data consistency. From scanning a barcode to printing a receipt, dozens of database operations execute in milliseconds. This guide covers the complete schema design for a modern POS system — including product management, inventory tracking, sales transactions, customer relationships, and reporting.
Core Tables: Products and Categories
The products table is the foundation. Fields include product_id (PK), sku (unique stock-keeping unit), barcode, product_name, description, category_id (FK), brand, unit_price, cost_price, tax_rate, unit_type (piece/kg/liter), is_active, and image_url. The categories table organizes products hierarchically: category_id (PK), name, parent_category_id (self-referencing FK for subcategories), and sort_order. A well-designed category tree supports departmental reporting — for example, comparing grocery sales against electronics within the same query using recursive CTEs.
Inventory and Stock Management
The inventory table tracks stock levels across multiple locations: inventory_id (PK), product_id (FK), store_id (FK), quantity_on_hand, quantity_committed (reserved for open orders), reorder_point, reorder_quantity, and last_count_date. A stock_movements table records every inventory change: movement_id (PK), product_id (FK), store_id (FK), movement_type (receipt/sale/return/adjustment/transfer), quantity, reference_document, movement_date, and performed_by. This audit trail is essential for identifying discrepancies during physical inventory counts and for loss prevention analysis.
Sales Transaction Schema
The sales schema follows a header-detail pattern. The sales_orders table (header) records: order_id (PK), store_id (FK), customer_id (FK), employee_id (FK — the cashier), order_date, order_time, subtotal, discount_total, tax_total, grand_total, payment_status, and order_status. The sales_order_items table (detail) captures each line item: order_item_id (PK), order_id (FK), product_id (FK), quantity, unit_price_at_sale, discount_percent, line_total, and returned_quantity. Storing the unit_price_at_sale is critical because product prices change over time — querying the current price for a historical sale would produce inaccurate reports.
Payment Processing
The payments table handles multiple tender types per transaction: payment_id (PK), order_id (FK), payment_method (cash/card/UPI/credit/ voucher), payment_amount, reference_number (for card/UPI transactions), payment_date, and is_verified. For split payments (e.g., paying partly with cash and partly with card), multiple rows link to the same order_id. A separate registers table manages cash drawer operations: register_id (PK), store_id (FK), opening_balance, closing_balance, opened_by, closed_by, opening_date, closing_date, expected_cash, and variance. End-of-day reconciliation compares expected cash (opening balance plus cash payments minus cash payouts) against the actual counted amount.
Customer Relationship Management
The customers table stores: customer_id (PK), first_name, last_name, phone, email, date_of_birth, anniversary_date, loyalty_points, total_spent, registration_date, and is_vip. A loyalty_transactions table tracks points earned and redeemed per order. For retail chains, a customer_addresses table supports delivery orders with multiple addresses. The customer table integrates with the sales schema through the customer_id FK in sales_orders, enabling features like purchase history, personalized offers, and automated birthday discounts.
Multi-Store Architecture
For chain operations, the stores table defines each location: store_id (PK), store_name, address, city, state, phone, tax_registration_number, and is_active. The store_inventory view joins products with inventory across stores to show real-time stock levels at each location. Transfer orders between stores use a transfer_orders table: transfer_id (PK), from_store_id (FK), to_store_id (FK), product_id (FK), quantity, status (requested/approved/shipped/received), request_date, and completion_date. This structure supports centralized reporting while allowing decentralized operations.
Reporting and Analytics Tables
Pre-aggregated reporting tables prevent slow queries on large datasets. A daily_sales_summary table stores: summary_id (PK), store_id (FK), sale_date, total_orders, total_sales, total_tax, total_discounts, average_order_value, and payment_method_breakdown (JSON). A product_sales_daily table tracks per-product performance: product_id (FK), store_id (FK), sale_date, quantity_sold, revenue, and cost_of_goods_sold. These tables are populated by scheduled jobs or database triggers, keeping the main transaction tables lean while providing instant report generation for managers.
Using the Business System Prompt Builder
Manually writing CREATE TABLE statements for all these entities takes hours. The Business System Prompt Builder generates the entire POS schema in seconds. Select "Point of Sale (POS)" as the system type, enable modules like Product Catalog, Inventory, Sales, Payments, Customers, and Multi-Store, choose your database (MySQL, PostgreSQL, SQL Server, or SQLite), and get a complete prompt with tables, relationships, indexes, and sample data ready for ChatGPT, Claude, or Gemini.
POS Database Best Practices
Use database transactions for every sale — if any line item insert fails, the entire order should roll back. Index the barcode column for sub-millisecond product lookups at the register. Implement optimistic locking (version number column) on inventory tables to prevent overselling during high-concurrency periods. Partition sales_order_items by order_date for fast historical queries. Always store monetary values in the smallest currency unit (cents/paisa) as integers to avoid floating-point rounding errors. For offline POS scenarios (common in retail), design the schema with a sync_status column and local UUID generation so registers can operate without internet and sync later.