🚀 Professional Database Schema — Food Delivery App (PostgreSQL + Node.js) 1. 🧭 System Overview Purpose - Design a high-performance, scalable database schema for a Food Delivery App supporting: - Order placement & lifecycle - Real-time tracking - Secure payments Core Entities - Users (customers, delivery agents, admins) - Restaurants & menus - Orders & order items - Payments & transactions - Delivery & tracking Architecture Distributed microservices architecture - User Service - Order Service - Payment Service - Delivery Service Database Strategy - PostgreSQL (primary OLTP database) - Redis (caching & sessions) - Event-driven communication (Kafka / RabbitMQ) 2. 🔗 Entities & Relationships Primary Relationships - One User → Many Orders - One Restaurant → Many Menu Items - One Order → Many Order Items - One Order → One Payment - One Order → One Delivery - One Delivery Agent → Many Deliveries Many-to-Many Relationships - Users ↔ Addresses (via user_addresses) - Restaurants ↔ Categories (via restaurant_categories) 3. 🧱 Entity Structures User - Fields: - id (UUID, PK) - name (VARCHAR, required) - email (VARCHAR, unique, indexed) - phone (VARCHAR, unique) - password_hash (TEXT, required) - role (ENUM: customer, delivery, admin) - created_at (TIMESTAMP) - Indexing: - email + phone (for fast login/search) Address - id (UUID, PK) - user_id (FK → User.id) - address_line (TEXT) - city, state, pincode (VARCHAR) - latitude, longitude (DECIMAL) - is_default (BOOLEAN) Restaurant - id (UUID, PK) - name (VARCHAR, indexed) - location (TEXT) - rating (FLOAT) - is_active (BOOLEAN) - created_at (TIMESTAMP) - Index: - location + rating (for search optimization) Menu Item - id (UUID, PK) - restaurant_id (FK) - name (VARCHAR) - description (TEXT) - price (DECIMAL) - is_available (BOOLEAN) - category_id (FK) - Index: - restaurant_id + category_id Order - id (UUID, PK) - user_id (FK) - restaurant_id (FK) - status (ENUM: pending, confirmed, preparing, delivered, cancelled) - total_amount (DECIMAL) - created_at (TIMESTAMP) - updated_at (TIMESTAMP) Index: user_id + created_at status (for tracking queries) OrderItem - id (UUID, PK) - order_id (FK) - menu_item_id (FK) - quantity (INTEGER) - price_at_order (DECIMAL) Payment - id (UUID, PK) - order_id (FK, unique) - amount (DECIMAL) - status (ENUM: pending, success, failed) - method (ENUM: card, UPI, wallet) - (VARCHAR, encrypted) - created_at (TIMESTAMP) Delivery - id (UUID, PK) - order_id (FK) - delivery_agent_id (FK → User.id) - status (ENUM: assigned, picked, delivered) - estimated_time (TIMESTAMP) - actual_time (TIMESTAMP) TrackingLog - id (UUID, PK) - order_id (FK) - location (POINT / JSON) - timestamp (TIMESTAMP) 4. 🧩 Normalization & Integrity Normalization - 1NF: Atomic fields, no repeating groups - 2NF: No partial dependencies (e.g., OrderItem fully dependent on composite logic) - 3NF: No transitive dependencies (separate Payment, Delivery) Denormalization (Selective) - Store price_at_order in OrderItem (to avoid price changes impact) - Store total_amount in Order for faster reads Constraints - Foreign key constraints with: - ON DELETE CASCADE (Order → OrderItems) - ON DELETE SET NULL (Delivery agent reassignment) - Unique: - email, phone, transaction_ref 5. ⚡ Performance Strategy Indexing - B-Tree indexes on: - user_id, restaurant_id - status fields - Partial indexes: - active restaurants - GIN index: - for full-text search (menu items) Partitioning - Orders table partitioned by: - date (monthly partitions) Caching - Redis for: - frequently accessed menus - active orders Concurrency - Use: - Row-level locking (FOR UPDATE) - Optimistic locking (version column) 6. 🔐 Security & Compliance (PCI-DSS) Encryption - At Rest: - AES-256 for sensitive fields - In Transit: - TLS 1.3 Sensitive Data Handling - Never store raw card details - Use tokenization for payments Access Control - RBAC: - Admin, Customer, Delivery roles - Row-level security for user-specific data Compliance - PCI-DSS: - Payment isolation via separate service - GDPR: - Data anonymization & deletion support 7. 📈 Scalability & Evolution Horizontal Scaling - Read replicas for heavy read traffic - Sharding strategy: - Users by region - Orders by time Schema Evolution - migrations (e.g., Flyway) - Backward compatibility Multi-Region - Geo-replication - Region-based routing 8. 🛡️ Reliability & Edge Cases Handling Edge Cases - Null handling: - Optional fields clearly defined - Duplicate prevention: - Unique constraints + idempotency keys - Backup Strategy - Daily full backup - Incremental backups every hour - Failover - Automatic failover with replicas - Transaction Model - ACID compliance for: - Payments - Order creation 9. 🧪 Example Queries (Text Only) - Create Order - Insert into Order - Insert multiple OrderItems - Commit transaction - Fetch User Orders - Join Order + Restaurant - Filter by user_id - Sort by created_at DESC - Limit with pagination - Track Order - Join Order + Delivery + TrackingLog - Filter latest location - Analytics Query - Total revenue per restaurant: - SUM(total_amount) - GROUP BY restaurant_id 10. 🚀 Optional Enhancements - Event-Driven Architecture - Emit events: - OrderPlaced - PaymentCompleted - DeliveryAssigned Analytics Integration - Use data warehouse (BigQuery / Redshift) - Audit Logging - Track all changes: - user actions - order status changes API-Ready Design - Schema aligned with REST/GraphQL - Clear entity boundaries for services