📊 POS-ER-Diagram
This repository contains a standard Entity-Relationship (ER) diagram for a Point of Sale (POS) system integrated with accounting logic. The diagram defines all major tables, their relationships, and the reasoning behind their design.
The goal is to provide a scalable and normalized database model that supports sales, inventory, and financial tracking for retail or service businesses.
📁 Tables Overview
- Users
Stores system users (cashiers, managers, admins).
Required for authentication, permissions, and tracking who performs sales.
- Customers
Holds customer details (name, contact info, type).
Needed for tracking purchases, returns, and loyalty programs.
- Suppliers
Stores supplier/vendor details.
Supports purchase orders and inventory restocking.
- Products
Core table containing product details (name, code, price, category, unit, tax).
Central to both sales and inventory control.
- Categories
Groups products (e.g., beverages, electronics, clothing).
Helps reporting and filtering in POS interfaces.
- Sales (Invoices)
Header table for each sale/transaction.
Tracks invoice number, date, customer, user, total, payment method.
Connected to Sales_Items for line details.
- Sales_Items
Line items for each invoice.
Contains product ID, quantity, price, discount, and subtotal.
- Purchases (GRN / Purchase Invoices)
Header table for recording stock purchases.
Tracks supplier, invoice number, total cost, and user.
- Purchase_Items
Line details of purchase invoices.
Contains product, quantity, cost, and total.
- Inventory / Stock Movements
Tracks increases/decreases in product stock.
Generated automatically from sales, purchases, and adjustments.
Ensures accurate stock-on-hand and prevents overselling.
- Payments
Handles customer payments (cash, card, credit).
Connected to invoices to ensure proper accounting.
- Accounts / Ledger
Provides basic accounting logic.
Tracks transactions (sales revenue, expenses, receivables, payables).
Supports double-entry bookkeeping for financial reports.
- Returns (Sales_Returns & Purchase_Returns)
Optional but important for real-world POS.
Records product returns from customers or to suppliers.
🔗 Relationships
Users → Sales / Purchases: Who performed the transaction.
Customers → Sales: Which customer made the purchase.
Suppliers → Purchases: Which supplier provided the stock.
Sales ↔ Sales_Items: One-to-many (invoice with multiple products).
Purchases ↔ Purchase_Items: One-to-many (purchase with multiple products).
Products ↔ Sales_Items / Purchase_Items: Track product-level movement.
Sales / Purchases → Accounts: Each transaction posts to accounting entries.
Inventory ↔ Products: Updates stock per product.
🎯 Why This Structure?
Scalability – can handle single-store or multi-branch setups.
Accuracy – double-entry accounting ensures financial reliability.
Traceability – every sale, purchase, and return is linked to users, customers, and suppliers.
Inventory Control – stock movement tables keep stock levels consistent.
Compliance – supports auditing and financial reporting.
📌 Use Case
This ER model can be used as a foundation for:
Retail POS applications
Restaurant ordering systems
Pharmacy management
Any sales-based system requiring accounting integration
✍️ Author: Najibullah Jafari